[GENERAL] org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command

Hello, Running psql table updates() by using org.springframework.scheduling.quartz.JobDetailFactoryBean cronjob from web application. Got the following exception: org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command Re-run the same cronjob several

[GENERAL] drop index without accessexclusivelock in transaction (JAVA)

Hello, Through java jdbc, is it possible that we do the following steps without accessexclusivelock for index: setautocommit(false); drop index1, 2,; insert millions records set index1,2... commit; Found this post, but it says only within psql block begin/commit, users are able to do

[GENERAL] Simple way to load xml into table

Hello, For psql 8.3, is there a simple way to load xml file into table please? E.g., True test1 e1 false test2 Results: t1 (c1 text, c2 text, c3 text): c1| c2 | c3 - true| test1 | e1 false | test2 | null ..

[GENERAL] blank in query - cannot return results

Hello, PostgreSQL 8.3.18 on x86_64-linux-gnu, query: select * from table_name where col1 ='Abc, test'; select * from table_name where col1 ilike '%Abc, test%'; NO result returned. But run: select * from table_name where col1 ilike '%Abc,%test%'; -- remove blank Result is returned.

Re: [GENERAL] blank in query - cannot return results

Hello, PostgreSQL 8.3.18 on x86_64-linux-gnu, query: select * from table_name where col1 ='Abc, test'; select * from table_name where col1 ilike '%Abc, test%'; NO result returned. But run: select * from table_name where col1 ilike '%Abc,%test%'; -- remove blank Result is

[GENERAL] CopyManager(In/out) vs. delete/insert directly

(false); delete t2 where pk.cols in t1; insert t2 select * from t1; Thank you Emi

[GENERAL] org.postgresql.copy.CopyManager which JDBC version ?

Hello, May I know from which version of psql JDBC driver, it provides org.postgresql.copy.CopyManager please? postgresql-8.3-605.jdbc4.jar does not have this class. Thanks a lot! Emi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription

[GENERAL] idle question

from pg_stat_activity where usename ='test'; current_query -- IDLE IDLE IDLE IDLE IDLE IDLE IDLE IDLE IDLE IDLE Thanks a lot! Emi

[GENERAL] which Update quicker

and t1.c_Na.c_N; (2) update t1 set c1 = a.c1 , c2 = a.c2, ... c_N = a.c_N from a where pk AND ( t1.c1 a.c1 OR t1.c2 a.c2. t1.c_N a.c_N) Or other quicker way for update action? Thank you Emi -- Sent via

[GENERAL] FATAL: terminating connection due to administrator command

n due to administrator command"? Thanks a lot! Emi

[GENERAL] drop/create/alter amongst difference schemas as non-super user

schema1.v1; \c - schema2; create schema2.v2; commit; This way the commit would complain and have to type pwd for diff schema owners. Is there a better way of doing this? Thanks a lot! Emi -- Note: . Views need to be created under difference schemas and created by that schema owner for now

Re: [GENERAL] alter column to varchar without view drop/re-creation

; DROP VIEW some_view ; ALTER TABLE some_table ALTER COLUMN some_col TYPE new_type; CREATE OR REPLACE VIEW some_view SELECT * FROM some_table; COMMIT; Comparing with the pg_attribute action, this approach would be the last one since there are too many view dependencies. Thanks a lot! Emi -- Sent

[GENERAL] alter column to varchar without view drop/re-creation

to varchar and no date/numeric changes. Thanks a lot! Emi --- PostgreSQL 8.3.18 on x86_64

Re: [GENERAL] alter column to varchar without view drop/re-creation

Hello list, May I know is there a way to "alter column type to varchar" (previous is varchar(***)) without view drop/re-creation? Basically, looking for a way to change column without have to drop/re-create dependent views.

Re: [GENERAL] alter column to varchar without view drop/re-creation

the change? Would there be any other potential problems for this approach? If not, I will adopt this approach since we have many view dependencies and it seems that this was the best way to avoid view drop/re-creation for now. If there are other ways, please do let me know. Thanks a lot! Emi

[GENERAL] Alter column with views depended on it without drop views

. Thanks a lot! Emi -- PostgreSQL 8.3.18 on x86_64-unknown-linux-gnu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Grant - create schema permission for a user

Good morning, I am looking for the command to grant create schema permission for a user. Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] spring a string to rows (Postgresql 8.4)

Hello, Is there a function to split a string to different rows? For example, t1(id, col1) values(1, 'a, b, c'); select id, string_split_to_row(col1, ','); Return: = 1, a 1, b 1, c Thanks alot! Emi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

...encoding...=ISO... No. This does not work. For now, through the following method, all letters are correctly transformed except È. What does OP stand for? Emi -- public static String utf8_to_latin1(String str) throws Exception { try { if(str.indexOf(È)=0) { str

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

. Thanks alot! Emi -- public static String utf8_to_latin1(String str) throws Exception { try { if(str.indexOf(È)=0) { str = str.replaceAll(È, E); } byte[] convertStringToByte = str.getBytes(UTF-8); str

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

for this! Emi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

On 12/14/2012 01:37 PM, Emi Lu wrote: Hello All, Meh. That character renders as \310 in your mail, which is not an assigned code in ISO 8859-1. The numerically corresponding Unicode value would be U+0090, which is an unspecified control character. Oh, scratch that, apparently I can't do hex

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

on the target database. The JDBC drivers will handle all the conversion. Do NOT manually convert the data. getString() and setString() will do everything correctly. I am not using stmt directly but through Mybatis for all db transactions. So, this approach will not work. Thanks. -- Emi -- Sent via

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

not be mapped correctly in (2). I was thinking that psql may have methods could help this. But it seems that I have to try from java coding side :-( -- Emi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

[GENERAL] JDBC to load UTF8@psql to latin1@mysql

Good morning, Is there a simple way to load UTF8 data in psql to mysql(with latin1 encoding) through JDBC? Thanks a lot! Emi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

Is there a simple way to load UTF8 data in psql to mysql(with latin1 encoding) through JDBC? JAVA codes work for most of characters, but not -È. Someone knows why the following codes cannot load -È to mysql@latin1? Thanks a lot! -- public static String utf8_to_latin1(String str) throws

Re: [GENERAL] Great site for comparing databases (or anything else)

the results shown in the website. I would say that it is always good to have something to start with. And I vote for it. -- Emi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Simple method to format a string?

Good morning, Is there a simply method in psql to format a string? For example, adding a space to every three consecutive letters: abcdefgh - *** *** *** Thanks a lot! Emi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Simple method to format a string?

with several more replace(s), regexp_replace will provide me the expecting result. Thanks! Emi -- select regexp_replace( replace( replace(col-val, ' ', ''), '-', ''), replace... '(...)', E'\\1 ', 'g') from tn; -- Sent via pgsql-general mailing list (pgsql-general

[GENERAL] Simple way to get missing number

Good morning, May I know is there a simple sql command which could return missing numbers please? For example, t1(id integer) values= 1, 2, 3 500 select miss_num(id) from t1 ; Will return: === 37, 800, 8001 Thanks a lot! Emi -- Sent via pgsql-general mailing

Re: [GENERAL] Simple way to get missing number

Aha, generate_series, I got it. Thank you very much!! I also tried left join, it seems that left join explain analyze returns faster comparing with except: select num as missing from generate_series(5000, 22323) t(num) left join t1 on (t.num = t1.id) where t1.id is null limit 10; Emi

Re: [GENERAL] [SQL] Simple way to get missing number

I got it and thank you very much for everyone's help!! It seems that left join where is null is faster comparing with except. And my final query is: select num as missing from generate_series(5000, #{max_id}) t(num) left join t1 on (t.num = t1.id) where t1.id is null; Emi On 04/24/2012

[GENERAL] Temporally disabled foreign key constraint check?

Good morning, Is there a way to temporally disabled foreign key constraints something like: SET FOREIGN_KEY_CHECKS=0 When population is done, will set FOREIGN_KEY_CHECKS=1 Thanks a lot! Emi -- Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 em...@encs.concordia.ca+1 514 848

Re: [GENERAL] Temporally disabled foreign key constraint check?

Thank you first. I believe that upate pg_class can only be done by superuser, right? Besides, if I need the whole schema's foreign keys to be disabled and then enabled later. Is there a simple command could do it? Similar to mysql's set FOREIGN_KEY_CHECKS = false/true? Emi On 10/21/2011

[GENERAL] ERROR: malformed record literal: ,DETAIL: Missing left parenthesis?

(z_drop) AS INSERT INTO z_drop VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21) ; EXECUTE test('', '1', '1', '1', '1', '1', '1', '1', '1', '', 1, 1, '1', '0', '0', '0', '0', '0', '0', '0', '0') ; Thank you, Emi -- Sent via pgsql

[GENERAL] postgresql-9.0-801.jdbc4.jar always cause org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled Exception

transaction. Cause: org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled. While for 8.4-702 JDBC 4, the same codes, no error at all. Is this a bug for postgresql-9.0-801.jdbc4.jar? Thanks a lot! Emi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] postgresql-9.0-801.jdbc4.jar always cause org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled Exception

know you need one. Exactly. I need to know in spring3.0.5 + mybatis + jdbc9 where to setup autocommit= false. For spring3.0.5 + mybatis + jdbc8, the default is autocommit = false. Thank you, Emi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

[GENERAL] Re: 500KB PDF files for postgresql8.3, which is the most efficient way?

) Save oids of pdfs into table (3) Save pdf files as bytea column in psql8.3 Pros and cons for (1), (2), (3), which is the most efficient way? Thanks a lot! Emi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org

[GENERAL] 500KB PDF files for postgresql8.3, which is the most efficient way?

and cons for (1), (2), (3), which is the most efficient way? Thanks a lot! Emi On 05/27/2011 12:45 AM, Jasen Betts wrote: On 2011-05-26, Bosco Ramapostg...@boscorama.com wrote: select * into temp table foo from maintable where primcol=123; update foo set primcol = 456; insert

[GENERAL] 500KB PDF saving into large object , what is the table size?

Hello, A question about large object column type in postgresql8.3. A pdf file=500KB. If saving into large object column, will the table size be around 500KB? If larger than 500KB, what could be the proximate size? Thanks a lot! Emi -- Sent via pgsql-general mailing list (pgsql-general

[GENERAL] How to realize ROW_NUMBER() in 8.3?

Hello, ROW_NUMBER() is only ready in 8.4. For 8.3, is there a simple way to get row_number select row_number(), col1, col2... FROM tableName Thanks a lot! 丁叶 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

[GENERAL] dblink server closed the connection unexpectedly - Exception

was lost. Attempting reset: Failed. ! Note: sql is changed to i When re-start database server, the error disappear automatically. Could someone let me what may cause this exception please? Thanks alot! -- Emi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

[GENERAL] Fwd: dblink server closed the connection unexpectedly - Exception

is open DETAIL: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request In a real example, how do I interpret this? Still what may cause Port is open exception? Thanks alot! -- Emi -- Sent via pgsql-general mailing

[GENERAL] pdf saving into DB vs. saving file location ?

Hello list, A question about saving PDF (size around 160kb) into postgresql large object columns vs. saving into a directory. May I know the performance differences? Pros and crons please? Each year, 20 new pdfs will be saved into DB (each around 160KB). Thanks a lot! Emi -- Sent via pgsql

Re: [GENERAL] read and restore deleted record

Start from vacuum feature information from PGSQL helps documentation, it telling me that Postgresql didn’t delete data permanently when we execute delete command, it just made the data invalid. By following this email archive : http://archives.postgresql.org/pgsql-admin/2005-01/msg00176.php I

Re: [GENERAL] resizing a varchar column on 8.3.8

On 01/15/2011 04:22 PM, Jon Hoffman wrote: Hi, I found a post with some instructions for resizing without locking up the table, but would like to get some re-assurance that this is the best way: http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data How

Re: [GENERAL] Graphical representation of query plans

pgadmin does it pretty nicely: http://pgadmin.org/images/screenshots/pgadmin3_macosx.png As shown in the mackintosh version, it is a very nice and helpful feature! I have pgadmin 1.2.0 for PostgreSQL 8.0.15 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2. I did not see this explain in

Re: [GENERAL] Graphical representation of query plans

Grzegorz Jaśkiewicz wrote: pgadmin does it pretty nicely: http://pgadmin.org/images/screenshots/pgadmin3_macosx.png As shown in the mackintosh version, it is a very nice and helpful feature! I have pgadmin 1.2.0 for PostgreSQL 8.0.15 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2.

[GENERAL] Change view definition - do not have to drop it

Original view1 (col1 bpchar, col2 varchar). Now I need update view1 definition to create or replace view view1 as select col1, col2 from new_table; However, col1 in new_table is not bpchar. This gives me headache! There are tens of dependent views based on view1, so I cannot just drop view1

Re: [GENERAL] Change view definition - do not have to drop it

Now I need update view1 definition to create or replace view view1 as select col1, col2 from new_table; However, col1 in new_table is not bpchar. This gives me headache! There are tens of dependent views based on view1, so I cannot just drop view1 and recreate it. How I can redefine

Re: [GENERAL] How can I look at a recursive table dependency tree?

A. Kretschmer wrote: In response to Igor Katson : I want to DROP CASCADE a table, but I am afraid that amoung numerous recursive dependencies there will be smth, that I don't want to drop. Is there a way to watch all dependencies recursively without doing a drop? You can walk through

Re: [GENERAL] What may cause - Connection rejected: FATAL: Ident authentication failed for user?

My daily data population cronjob(around 1 hour) terminated at the middle and raised the following error this morning: Connection rejected: FATAL: Ident authentication failed for user schema_owner_name. Could anyone tell me what might cause the problem please? This does not seem logical.

[GENERAL] What may cause - Connection rejected: FATAL: Ident authentication failed for user?

Good morning, My daily data population cronjob(around 1 hour) terminated at the middle and raised the following error this morning: Connection rejected: FATAL: Ident authentication failed for user schema_owner_name. Could anyone tell me what might cause the problem please? Thanks a lot!

[GENERAL] Dump all objects under a specific schema

Hello List, Is there a way that I can only dump all objects under a specific schema? I'd like to dump all tables, views' definition and data under a specific schema. Thank you! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Date for a week day of a month

generate_series that's a good one! Thank you! On 7/3/07, Emi Lu [EMAIL PROTECTED] wrote: Can I know how to get the date of each month's last Thursday please? Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday'); Result: 2007-04-26 you can easily do it without functions

[GENERAL] Check whether two strs have at least one shared character.

Hello, Is there a simple way/command/function to check whether two strings have at least one overlap character please? For example, str1: 05baaa str2: ooboo Query: select str1 ??? str2 ; Result: true Because b is in both str1 and str2. Thank you! ---(end of

[GENERAL] Date for a week day of a month

Hello, Can I know how to get the date of each month's last Thursday please? For example, something like Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday'); Result: 2007-04-26 Thank you! ---(end of broadcast)--- TIP 1: if

Re: [GENERAL] Date for a week day of a month

Thank you all for your inputs! Based on your inputs, made it a bit change to my application: == DROP FUNCTION test_db.lastWeekdayDate (date, varchar) ; CREATE OR REPLACE FUNCTION test_db.lastWeekdayDate

[GENERAL] postgresql 8.2 Installation error at gmake

Hello, Tried to install 8.2, configuration passed, but when running gmake, got the following error, any clues? Thanks a lot! Step1 ./configure --prefix=/local/postgresql --datadir=/postgreSQL_data --without-docdir --without-readline --disable-spinlocks --without-zlib PASS!

Re: [GENERAL] postgresql 8.2 Installation error at gmake

Hello, Tried to install 8.2, configuration passed, but when running gmake, got the following error, any clues? Please provide more details -- what operating system is this? i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 Step1 ./configure --prefix=/local/postgresql

Re: [GENERAL] postgresql 8.2 Installation error at gmake

Tried to install 8.2, configuration passed, but when running gmake, got the following error, any clues? Step1 ./configure --prefix=/local/postgresql --datadir=/postgreSQL_data --without-docdir --without-readline --disable-spinlocks --without-zlib Please note that --datadir

Re: [GENERAL] postgresql 8.2 Installation error at gmake

Hello, Do you happen to have a previous installation at /local/postgresql? I think the presence of an older libpgport.a there could be causing the confusion. (1) \rm -r /local/postgresql (2) ./configure --prefix=/local/postgresql --without-docdir --without-readline --without-zlib

Re: [GENERAL] postgresql 8.2 Installation error at gmake

Tried to install 8.2, configuration passed, but when running gmake, got the following error, any clues? Please provide more details -- what operating system is this? i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 Step1 ./configure --prefix=/local/postgresql

Re: [GENERAL] postgresql 8.2 Installation error at gmake

Hello, Configure will work anyway without the --disable-spinlock; you're wasting your time with that option. Anyway, please note that your linker problem does not seem to be related to any of these options. Do you happen to have a previous installation at /local/postgresql? I think the

[GENERAL] Function in psql to Compare two numbers and return the bigger value

HEllo, I am looking for a psql method to get the bigger value of two numbers. For example, methodName(12.6, 3.8) Will return 12.6 Thanks! ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] Function in psql to Compare two numbers and return the bigger value

I am looking for a psql method to get the bigger value of two numbers. For example, methodName(12.6, 3.8) select greatest(12.6,3.8); It does not work for me, select greatest(12.6,3.8); ERROR: function greatest(numeric, numeric) does not exist HINT: No function matches the given name

Re: [GENERAL] Function in psql to Compare two numbers and return the bigger value

Emi Lu wrote: I am looking for a psql method to get the bigger value of two numbers. For example, methodName(12.6, 3.8) select greatest(12.6,3.8); It does not work for me, select greatest(12.6,3.8); ERROR: function greatest(numeric, numeric) does not exist HINT: No function matches

[GENERAL] Load a csv file into a pgsql table

Greetings, *Except* copy command, are there other quick ways to load data from a csv file into a pgsql table please? Thanks a lot! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Load a csv file into a pgsql table

Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Emi Lu Sent: Tuesday, September 19, 2006 2:15 PM To: PgSQL General Subject: [GENERAL] Load a csv file into a pgsql table Greetings, *Except* copy command, are there other quick ways to load data from a csv file

[GENERAL] Length limit for query

Hello, It appears in MySql 3.23 the limit is 16 MB. In 4.0 and later, it is 1 GB http://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html Could someone tell me where I can find PostgreSQL doc about the query length please Tks a lot! Hello, Just curious to know whether postgresql has

Re: [GENERAL] gmake Errors: pg_backup_archiver.c: undefined reference

I did make distclean, and I retried configuration and , but I still saw the following errors: The only other theory I can think of is that the linker is picking up an old libpq.so from /home/postgresql/lib because of the -rpath switch ... which it should not be doing, but maybe it is

[GENERAL] gmake Errors: pg_backup_archiver.c: undefined reference to `lo_create'

Hello, When I tried to install postgresql 8.1.4, I got the following errors. Could someone tell me what are missing or wrong please? == ./configure --prefix='/home/postgresql/' --without-docdir --without-readline . gmake

[GENERAL] gmake Errors: pg_backup_archiver.c: undefined reference to `lo_create'

Hello, When I tried to install postgresql 8.1.4, I got the following errors. Could someone tell me what are missing or wrong please? == ./configure --prefix='/home/postgresql/' --without-docdir --without-readline . gmake

[GENERAL] delete is extremely slow

Hello, Tried to run: delete from tableA where id 60; It is extremely slow. id is integer and also the primary key. tableA has around 9 records. (I do not want to run truncate since I have some foreign key constraint setup based on tableA. Besides not all data I want to delete; only

Re: [GENERAL] delete is extremely slow

Tried to run: delete from tableA where id 60; It is extremely slow. id is integer and also the primary key. tableA has around 9 records. (I do not want to run truncate since I have some foreign key constraint setup based on tableA. Besides not all data I want to delete; only part of

Re: [GENERAL] delete is extremely slow

Emi Lu wrote: Tried to run: delete from tableA where id 60; It is extremely slow. id is integer and also the primary key. tableA has around 9 records. (I do not want to run truncate since I have some foreign key constraint setup based on tableA. Besides not all data I want

Re: [GENERAL] delete is extremely slow

Scott Marlowe wrote: On Fri, 2006-05-26 at 09:51, Emi Lu wrote: Emi Lu wrote: Tried to run: delete from tableA where id 60; It is extremely slow. id is integer and also the primary key. tableA has around 9 records. (I do not want to run truncate since I have some

Re: RES: [GENERAL] Add column and specify the column position in

Jim C. Nasby wrote: On Wed, May 17, 2006 at 05:48:52PM -0400, Emi Lu wrote: I think it is a very useful feature for postgresql to support it. If we have this feature supported, I do not have to recreate the table and resetup all foreign key constraints, views, triggers, etc

[GENERAL] Add column and specify the column position in a table

Hello, I am trying to insert one column to a specific position in a table. In mysql, I can do: . create table test(id varchar(3), name varchar(12)); . alter table test add column givename varchar(12) after id; I am looking for similar things in postgresql to add a new column to the correct

Re: RES: [GENERAL] Add column and specify the column position in

This feature seems by a mysql add tu create table command, only mysql can do dat. Alejandro Michelin Salomon I think it is a very useful feature for postgresql to support it. If we have this feature supported, I do not have to recreate the table and resetup all foreign key constraints,

[GENERAL] About checking all dead lock tables

Hello, How to check whether a table is locked? Is there a way that I can find all deadlocks under postgresql 8.0.1 ? Thanks alot! Ying Lu ---(end of broadcast)--- TIP 6: explain analyze is your friend

[GENERAL] using initcap() for the French charater

Hello, I am having a problem of using initcap() for the French charater. . create table test (id varchar(3)); . insert into test values('É'); . select initcap(id) from test; *Got ERROR:* invalid multibyte character for locale HINT: The server's LC_CTYPE locale is probably incompatible with

[GENERAL] Which error constant to use for permission deny error when updating a table that user is not allowd to

Hello, I am trying to catch permission denied exception. For example, user 1 is not allowed to update table 2, when user1 updated table 2 there is a permission excetpion. In http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html, I tried modifying_sql_data_not_permitted ,

[GENERAL] Getting more information about errorcodes such as when these error1 happen

Good morning, In my plpgsql functions I use exception when... to catch possible exceptions of my data. I found postgresql error code track functions are very helpful. In my functions, I will not catch all error codes, but only the ones applying to my data operation . I found errorcodes

[GENERAL] about explain analyze cannot get detailed analyzed results for queries in a plpgsql function

Hello, I am using postgresql 8.0.1. I have a function (Pl/PgSQL) created with serveral complex queries inside. For example, create function f1() return void... begin ... query 1: insert into ... from A left join B... left join C where ... query 2: update T1 set ... from (X left join Y ..

Re: [GENERAL] About index for temporay table in a plpgsql function

Hello, The temporary table will be dropped automatically at the end of the function, right? A temp table will be dropped at the end of the connection. You can reuse it by adding ON COMMIT DELETE ROWS and then check if it exists in your function with this other function:

[GENERAL] About index for temporay table in a plpgsql function

Hello, A question about index for temporary table. Postgresql 8.0.1, in a pl/pgsql function: begin ... -- Part I create temporal table t1 as ((select ... from .. left join . where ... ) union (select .. from ... left join ... where ... )); create index idx_t1_cols on t1(col1, col2)

Re: [GENERAL] in Pl/PgSQL, do commit every 5000 records

Hi Merlin, In general, if you have the choice between looping over a large result in a stored procedure (or, even worse, in a client app) and letting the backend do the looping, then letting the backend handle it is nearly always faster. There are different reasons why a large query

[GENERAL] Update value to the first character is capital and the rest is small

Hello all, Does anyone have available plpgsql codes to update all capital letters in a column to the first character is capital and the rest is small ? For example, in tableA(id, description) 001, 'ZHANG ZHE XIN' = 'Zhang Zhe Xin' 002, 'LIU,

Re: [GENERAL] Update value to the first character is capital and

I got the answer from the docs. |initcap|(text) thanks anyway, Ying Hello all, Does anyone have available plpgsql codes to update all capital letters in a column to the first character is capital and the rest is small ? For example, in tableA(id, description) 001, 'ZHANG ZHE XIN'

[GENERAL] in Pl/PgSQL, do commit every 5000 records

Good morning, In a plpgsql function, I am trying to insert 900, 000 records into several tables. I remembered people mentioned before that it is better and more efficient to commit actions for let's say every 5000 records' insertion. May I get more inputs about why and how this commit can

[GENERAL] About updates

Hello, postgresql 8.0.1, in a plpgsql function To update columns' values in a table (without OID), if I ran: 1. update table1 set col1 = ..., col2 = ... ... col100 = or 2. update table1 set col1 = ... update table1 set col100 = way 1 only has one disk I/O, right? While way 2 is more time

Re: [GENERAL] in Pl/PgSQL, do commit every 5000 records

Hi Bruno, You can't do commits inside of a function. The example I have is: CREATE OR REPLACE function test() returns boolean AS $$ DECLARE ... ... counterINTEGER := 0; BEGIN ... ... query_value := ' .' ; OPEN curs1 FOR EXECUTE query_value; LOOP FETCH curs1

Re: [GENERAL] About updates

Thanks Andreas. That was a quick response. So way 1 must be quicker. am 10.03.2006, um 10:46:39 -0500 mailte Emi Lu folgendes: Hello, postgresql 8.0.1, in a plpgsql function To update columns' values in a table (without OID), if I ran: 1. update table1 set col1 = ..., col2

Re: [GENERAL] in Pl/PgSQL, do commit every 5000 records

I got the answer. Although the compile passed, when it reaches 5000, the commit command causes a SPI_ERROR_TRANSACTION exception. Thank you for all your hint. You can't do commits inside of a function. The example I have is: CREATE OR REPLACE function test() returns boolean AS $$ DECLARE

Re: [GENERAL] in Pl/PgSQL, do commit every 5000 records

Florian G. Pflug wrote: Emi Lu wrote: The example I have is: CREATE OR REPLACE function test() returns boolean AS $$ DECLARE ... ... counterINTEGER := 0; BEGIN ... ... query_value := ' .' ; OPEN curs1 FOR EXECUTE query_value; LOOP FETCH curs1 INTO studid

Re: [GENERAL] in Pl/PgSQL, do commit every 5000 records

Florian G. Pflug wrote: snipped code of stored procedure Are you aware of the insert into table (field1, ..., fieldn) select val1, .., valn from command? It'd be much faster to use that it it's possible... greetings, Florian Pflug It did faster. Thank you Florian. Could you

[GENERAL] About array in PlPgsql trigger function

Hello, In PostgreSQL 8.0.1 (plpgsql), I was trying to use array in the trigger function. DECLARE clear_id_colValueArrVARCHAR[100]; BEGIN clear_id_colValueArr[1] := NEW.clear_id1; clear_id_colValueArr[2] := NEW.clear_id2; clear_id_colValueArr[3] := NEW.clear_id3;

[GENERAL] column type varchar(128) not null default '' vs varchar(128)

Hello, When setuping column types, is there the big efficiency difference between the following two examples? col varchar(128) NOT NULL default '' vs. col varchar(128) Thanks a lot, Ying ---(end of broadcast)--- TIP 1: if

Re: [GENERAL] About when we should setup index?

Thank you very much Michael. Your inputs are very helpful for me. Just have one small question, the example you gave is based on postgresql 8.1, does it apply to PostgreSQL 8.0.1 as well (i686-pc-linux-gnu, compiled by GCC gcc 3.3.2)? - Emi . id is the primary key, so a default unique

  1   2   >