[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 it

[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 .. Tha

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 returned.

[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.

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

(2) setautoCommit(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 chang

[GENERAL] question

rent_query   --                     Thanks a lot! Emi

[GENERAL] which Update quicker

pk and t1.c_N <> a.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

[GENERAL] FATAL: terminating connection due to administrator command

ion 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

ther way I know to do this is to: BEGIN; 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 depe

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

? As for the "definitely test", you mean check view after 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

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.

[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

[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@pos

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 d

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

em specifically, I suspect they are using >> your default system encoding - so both may be using utf8 or iso8859. Thank you very much for all of your help 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

ransformed correctly. Thanks alot! Emi -- public static String utf8_to_latin1(String str) throws Exception { try { if(str.indexOf("È")>=0) { str = str.replaceAll("È", "E"); } byte[] conv

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

d "jdbc:mysql://.../mysql_db?...unicode...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 Exce

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

d 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/mailpr

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

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

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) thro

[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] Great site for comparing databases (or anything else)

ue. I saw lots of discussion about 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

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

conforming_strings. For example: E'\\& ' After combined with several more replace(s), regexp_replace will provide me the expecting result. Thanks! Emi -- select regexp_replace( replace( replace(col-val, ' ', ''), '-', ''),

[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: h

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.i

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 On

[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] 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&qu

[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

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

PREPARE test(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

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

ing upon the driver to do it for you; though there are always exceptions but you should code is so that you can request an auto-commit session when you 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

[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-genera

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

Pros 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 Rama wrote: select * into temp table foo from maintable where primcol=123; update foo set primcol = 456; insert into maintable s

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

(2) 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.postgresq

[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: http://www.postgresql.or

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

et dbname=abc connect_timeout=10’); Port 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?

[GENERAL] dblink server closed the connection unexpectedly - Exception

ver 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] 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 does

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.

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 view1

[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 an

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 pg_depe

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. ide

[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 yo

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 (dat

[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 posting/

[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 broadc

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 presen

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 --datadir=/postgreSQ

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 The

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 --datad

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 --datadir

[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! St

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

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 and

[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] Load a csv file into a pgsql table

/IT Systems Engineer -Original 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 fro

[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

[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 an

[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 pg_backup_archiver.o(.text+

[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 pg_backup_archiver.o(.text+0x

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 s

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

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

[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: 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 that are

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] 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 po

[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 th

[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" , "sql_rou

[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 in

[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 .. lef

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: http://www.milwauke

[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) tables

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] 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] 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 la

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 (, ..., ) select , .., 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 hint me why "insert into

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 I

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 $$ DECLAR

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 =

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 I

[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 ti

[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 sp

[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 posting/re

[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; ...

Re: [GENERAL] how to setup default privileges

When I first created an object, and did "\dp+ tableName" I got blank under "Access privileges". However, if I tried to grant any permission to a group/user, I will see the access privileges for the object owner. Example: 1. create table test(id char(3)); 2. \dp+ test; Schema| Name |

  1   2   >