[SQL] Select into
Hi Everyone, I have asked our DBA at work and h is not too sure either... so I thought it best to on the list. Basically, what I am after is a way to copy the contents of one record into another. Something like select into; but where the destination record already exists, as opposed to creating a new record. Thanks in advance for anything you might come up with. Warmest regards, Gavin Baumanis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select into
am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau' Baumanis folgendes: > Hi Everyone, > > I have asked our DBA at work and h is not too sure either... so I > thought it best to on the list. > > Basically, what I am after is a way to copy the contents of one record > into another. > Something like select into; but where the destination record already > exists, as opposed to creating a new record. insert into select from where ... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select into
On Thu, Mar 20, 2008 at 4:19 PM, A. Kretschmer < [EMAIL PROTECTED]> wrote: > am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau' Baumanis > folgendes: > > Hi Everyone, > > > > I have asked our DBA at work and h is not too sure either... so I > > thought it best to on the list. > > > > Basically, what I am after is a way to copy the contents of one record > > into another. > > Something like select into; but where the destination record already > > exists, as opposed to creating a new record. > > insert into select from where ... > > He specifically asked for where the destination record already exists, as opposed to creating a new record. I think an UPDATE with joins would be helpful. Though, it may become lengthy if the tables have too many columns. Can you post your exact requirement? -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad * 18° 32' 57.25"N, 73° 56' 25.42"E - Pune 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [SQL] Select into
HI Gurjeet, You're right. But what information do you need to know? The copy is inside the same table, so I don't understand why it (the required query ) would require any joins. Ie. I want to copy the contents of a row (but for the id column - of course) into a record in the same table. I am happy enough to give you a table schema, if that's required... but I just don't see why it would be needed - but of course am happy to be told something new! Thanks again. Gavin Baumanis On 20/03/2008, at 9:58 PM, Gurjeet Singh wrote: On Thu, Mar 20, 2008 at 4:19 PM, A. Kretschmer <[EMAIL PROTECTED] > wrote: am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau' Baumanis folgendes: > Hi Everyone, > > I have asked our DBA at work and h is not too sure either... so I > thought it best to on the list. > > Basically, what I am after is a way to copy the contents of one record > into another. > Something like select into; but where the destination record already > exists, as opposed to creating a new record. insert into select from where ... He specifically asked for where the destination record already exists, as opposed to creating a new record. I think an UPDATE with joins would be helpful. Though, it may become lengthy if the tables have too many columns. Can you post your exact requirement? -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad * 18° 32' 57.25"N, 73° 56' 25.42"E - Pune 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [SQL] Select into
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Ie. I want to copy the contents of a row (but for the id > column - of course) into a record in the same table. BEGIN; CREATE TEMP TABLE tempfoo AS SELECT * FROM foo WHERE id = 123; UPDATE tempfoo SET id = 456; DELETE FROM foo WHERE id = 456; INSERT INTO foo SELECT * FROM tempfoo; COMMIT; - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200803200737 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfiTIYACgkQvJuQZxSWSsiCMwCdESkEe8Hc5xHhJ2B3qX3V7EqX Z2IAoMy65D2OhdUpYVtfEq182PhfsEfZ =fx5V -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select into
On Thu, Mar 20, 2008 at 4:39 PM, Gavin 'Beau' Baumanis < [EMAIL PROTECTED]> wrote: > HI Gurjeet, > You're right. > > But what information do you need to know? > > The copy is inside the same table, so I don't understand why it (the > required query ) would require any joins. > > Ie. I want to copy the contents of a row (but for the id column - of > course) into a record in the same table. > > I am happy enough to give you a table schema, if that's required... but I > just don't see why it would be needed - but of course am happy to be told > something new! > Even a small example of what you wanted would have worked. Anyway, lets see if I can got your problem. There are two records in your table emp: id | name | salary - 21 | scott | 2000 31 | greg | 3000 So you want to copy all the data from 'scott' row on to 'greg' row, but keep the id (id obviously being your unique identifier). UPDATE emp SET (salary, name) = ( (select salary from emp where id = 21 ), (select name from emp where id = 21) ) where id = 31; HTH, Best regards, > Thanks again > > Gavin Baumanis > > > > On 20/03/2008, at 9:58 PM, Gurjeet Singh wrote: > > On Thu, Mar 20, 2008 at 4:19 PM, A. Kretschmer < > [EMAIL PROTECTED]> wrote: > > > am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau' Baumanis > > folgendes: > > > Hi Everyone, > > > > > > I have asked our DBA at work and h is not too sure either... so I > > > thought it best to on the list. > > > > > > Basically, what I am after is a way to copy the contents of one record > > > into another. > > > Something like select into; but where the destination record already > > > exists, as opposed to creating a new record. > > > > insert into select from where ... > > > > > He specifically asked for > > > > where the destination record already > exists, as opposed to creating a new record. > > > > > I think an UPDATE with joins would be helpful. Though, it may become > lengthy if the tables have too many columns. > > Can you post your exact requirement? > > -- > [EMAIL PROTECTED] > [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com > > EnterpriseDB http://www.enterprisedb.com > > 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad * > 18° 32' 57.25"N, 73° 56' 25.42"E - Pune > 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco > > http://gurjeet.frihost.net > > Mail sent from my BlackLaptop device > > > -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad * 18° 32' 57.25"N, 73° 56' 25.42"E - Pune 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [SQL] Select into
Gavin 'Beau' Baumanis wrote: The copy is inside the same table, so I don't understand why it (the required query ) would require any joins. Maybe you should use FROM clause in the update that references a row-valued subquery? craig=# create table x ( id serial, val integer ); NOTICE: CREATE TABLE will create implicit sequence "x_id_seq" for serial column "x.id" CREATE TABLE craig=# insert into x ( val ) values ( 4 ) , ( 6 ) ; INSERT 0 2 craig=# select * from x; id | val +- 1 | 4 2 | 6 (2 rows) craig=# update x set val = foundrow.val from ( select val from x where id = 2 ) as foundrow where id = 1 ; UPDATE 1 craig=# select * from x; id | val +- 2 | 6 1 | 6 (2 rows) craig=# insert into x ( val ) select generate_series(0,1); INSERT 0 10001 craig=# explain update x set val = foundrow.val from ( select val from x where id = 4123 ) as foundrow where id = 5912 ; QUERY PLAN - Nested Loop (cost=0.00..16.55 rows=1 width=14) -> Index Scan using x_id_idx on x (cost=0.00..8.27 rows=1 width=10) Index Cond: (id = 5912) -> Index Scan using x_id_idx on x (cost=0.00..8.27 rows=1 width=4) Index Cond: (public.x.id = 4123) (5 rows) Will that do the job? -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select into
On Thu, Mar 20, 2008 at 5:35 PM, Craig Ringer <[EMAIL PROTECTED]> wrote: > > craig=# explain update x set val = foundrow.val from ( select val from x > where id = 4123 ) as foundrow where id = 5912 ; > Thats nifty. -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad * 18° 32' 57.25"N, 73° 56' 25.42"E - Pune 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [SQL] Select into
Hi Everyone, I want to thank everyone for their help / suggestions... I really appreciate it. Though I think I have found a winner. craig=# update x set val = foundrow.val from ( select val from x where id = 2 ) as foundrow where id = 1 ; UPDATE 1 Very elegant, very clean... Very nice! Thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select into
craig=# update x set val = foundrow.val from ( select val from x where id = 2 ) as foundrow where id = 1 ; UPDATE 1 Thinking about it, it'd actually be better written as: UPDATE x SET val = foundrow.val FROM ( SELECT val FROM x AS x2 WHERE x2.id = 2 ) AS foundrow WHERE id = 1; ... because it's nicer to use a table alias for x within the subquery and elimate any ambiguity for the reader about which "id" you're referring to. After all, it's also valid to reference the "id "field of the "x" outside the subquery within it, like in the following valid but rather nonsensical query: UPDATE x SET val = (SELECT id+1) WHERE id = 1; Using the table alias will not change the query plan at all, it just makes the reference to "id" within the subquery unambiguous to the reader. Sorry for the repeat post. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select into
Gavin 'Beau' Baumanis wrote: The copy is inside the same table, so I don't understand why it (the required query ) would require any joins. Ie. I want to copy the contents of a row (but for the id column - of course) into a record in the same table. I think what you want is something like this: Given (col1 being the id or PK): col1 | col2 | col3 --+--+--- 1 | 123 | first record 2 | 456 | second record 3 | 789 | third record then update t1 set col2 = t1copy.col2, col3 = t1copy.col3 from t1 as t1copy where t1.col1 = 1 and t1copy.col1 = 3; will result in: col1 | col2 | col3 --+--+--- 1 | 789 | third record 2 | 456 | second record 3 | 789 | third record So, it is a join ... of a table with a virtual copy of itself. Joe -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select into
On Thu, Mar 20, 2008 at 5:40 PM, Joe <[EMAIL PROTECTED]> wrote: > Gavin 'Beau' Baumanis wrote: > > > > The copy is inside the same table, so I don't understand why it (the > > required query ) would require any joins. > > > > Ie. I want to copy the contents of a row (but for the id column - of > > course) into a record in the same table. > > I think what you want is something like this: > > Given (col1 being the id or PK): > > col1 | col2 | col3 > --+--+--- >1 | 123 | first record >2 | 456 | second record >3 | 789 | third record > > then > > update t1 set col2 = t1copy.col2, col3 = t1copy.col3 > from t1 as t1copy > where t1.col1 = 1 and t1copy.col1 = 3; > > will result in: > > col1 | col2 | col3 > --+--+--- >1 | 789 | third record >2 | 456 | second record >3 | 789 | third record > > So, it is a join ... of a table with a virtual copy of itself. > Except that it doesn't work... Did you try to execute that query; I am assuming not. Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad * 18° 32' 57.25"N, 73° 56' 25.42"E - Pune 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [SQL] Select into
Gurjeet Singh wrote: Except that it doesn't work... Did you try to execute that query; I am assuming not. It does, or at least a query written to work the same way works fine for me. Not only that, but at least in the presence of a unique index the query planner optimises it to the same query plan as the one I proposed. From my earlier test data: craig=# update x set val = x2.val from x as x2 where x.id = 1000 and x2.id = 1024; UPDATE 1 craig=# select * from x where id in (1000,1024); id | val --+-- 1024 | 1021 1000 | 1021 (2 rows) craig=# explain update x set val = x2.val from x as x2 where x.id = 1000 and x2.id = 1024; QUERY PLAN --- Nested Loop (cost=0.00..16.55 rows=1 width=14) -> Index Scan using x_id_idx on x (cost=0.00..8.27 rows=1 width=10) Index Cond: (id = 1000) -> Index Scan using x_id_idx on x x2 (cost=0.00..8.27 rows=1 width=4) Index Cond: (x2.id = 1024) (5 rows) The above query actually executes slightly faster, presumably because the query planner has to do less work to reach the same point than it does with the subquery-based one I proposed. You should probably use this one instead of the subquery one. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Rollback locks table - why?
Hello list, I am a bit confused. See the code below: BEGIN; SAVEPOINT sp1; INSERT INTO test(id,runde) VALUES(2, 0); --if there is a unique key violstion: ROLLBACK TO sp1; UPDATE test SET id = 1000 WHERE runde = 0; COMMIT; When I first run this statement, I do not get any error message, but also there's also no INSERT on table test. If I run just: INSERT INTO test(id,runde) VALUES(2, 0); on its own, it works. (i.e. I get a new row). If I run the transaction block from above again I get first a unique key violation (that is ok, because that's what I trying to check for) but there is NO rollback to sp1, only the "Unique Key" error message and after that I get the dreaded "current transaction is aborted" error message and the system reports an EXCLUSIVE LOCK on the table (in transaction). Any ideas what I am doing wrong? Using PostGreSQL 8.2.7 on Windows XP. Regards and many thanks in advance Jan -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Rollback locks table - why?
Hello list, I am a bit confused. See the code below: BEGIN; SAVEPOINT sp1; INSERT INTO test(id,runde) VALUES(2, 0); --if there is a unique key violstion: ROLLBACK TO sp1; UPDATE test SET id = 1000 WHERE runde = 0; COMMIT; When I first run this statement, I do not get any error message, but also there's also no INSERT on table test. If I run just: INSERT INTO test(id,runde) VALUES(2, 0); on its own, it works. (i.e. I get a new row). If I run the transaction block from above again I get first a unique key violation (that is ok, because that's what I trying to check for) but there is NO rollback to sp1, only the "Unique Key" error message and after that I get the dreaded "current transaction is aborted" error message and the system reports an EXCLUSIVE LOCK on the table (in transaction). Any ideas what I am doing wrong? Using PostGreSQL 8.2.7 on Windows XP. Regards and many thanks in advance Jan -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger -- GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen! Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback locks table - why?
Jan Peters wrote: If I run the transaction block from above again I get first a unique key violation (that is ok, because that's what I trying to check for) but there is NO rollback to sp1, only the "Unique Key" error message and after that I get the dreaded "current transaction is aborted" error message and the system reports an EXCLUSIVE LOCK on the table (in transaction). Am I correct in saying that your issue is really that you want an error to trigger an automatic rollback to the last savepoint, but it does not do so ? If you issue the ROLLBACK manually (after you get the constraint error or a "current transaction is aborted" message) does it work as you would expect? -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select into
Gurjeet Singh wrote: Except that it doesn't work... Did you try to execute that query; I am assuming not. Of course I did, do you think I create results by editing them into my email? The script: delete from t1; insert into t1 values (1, 123, 'first record'); insert into t1 values (2, 456, 'second record'); insert into t1 values (3, 789, 'third record'); select * from t1; update t1 set col2 = t1copy.col2, col3 = t1copy.col3 from t1 as t1copy where t1.col1 = 1 and t1copy.col1 = 3; select * from t1; select version(); The output: DELETE 3 INSERT 0 1 INSERT 0 1 INSERT 0 1 col1 | col2 | col3 --+--+--- 1 | 123 | first record 2 | 456 | second record 3 | 789 | third record (3 rows) UPDATE 1 col1 | col2 | col3 --+--+--- 2 | 456 | second record 3 | 789 | third record 1 | 789 | third record (3 rows) version PostgreSQL 8.1.9 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3 (1 row) And BTW, I also tried your UPDATE SET (salary, name) but that only works on PG 8.2 and above. I don't see why my query would fail in subsequent releases. Joe -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback locks table - why?
Hello Craig, > Jan Peters wrote: > > If I run the transaction block from above again I get first a unique key > violation (that is ok, because that's what I trying to check for) but > there is NO rollback to sp1, only the "Unique Key" error message and after > that > I get the dreaded "current transaction is aborted" error message and the > system reports an EXCLUSIVE LOCK on the table (in transaction). > > > Am I correct in saying that your issue is really that you want an error > to trigger an automatic rollback to the last savepoint, but it does not > do so ? > > If you issue the ROLLBACK manually (after you get the constraint error > or a "current transaction is aborted" message) does it work as you would > expect? Yes, you are correct: I just want to issue an insertion of a row at the very beginning of an application start. So, when the table "test" is empty, the row with "runde = 0" should be inserted. If this row is already present, it should be updated. How do I do a "manual" ROLLBACK? Regards and thank you again in advance Jan -- GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen! Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select into
On Thu, Mar 20, 2008 at 7:08 PM, Joe <[EMAIL PROTECTED]> wrote: > Gurjeet Singh wrote: > > Except that it doesn't work... Did you try to execute that query; I am > > assuming not. > Of course I did, My bad... I did not run your query either, and based my assumption on my previous attempt that had thrown an error. Here's something similar to what had failed in my previous attemt: update t1 *as e1* set e1.col2 = t1copy.col2, e1.col3 = t1copy.col3 from t1 as t1copy where e1.col1 = 1 and t1copy.col1 = 3; As you can see, I had aliased the main table t1, and hence the error. Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad * 18° 32' 57.25"N, 73° 56' 25.42"E - Pune 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [SQL] Rollback locks table - why?
Jan Peters wrote: Yes, you are correct: I just want to issue an insertion of a row at the very beginning of an application start. So, when the table "test" is empty, the row with "runde = 0" should be inserted. If this row is already present, it should be updated. This is quite common - you might find past discussions about alternatives to the SQL:2003 MERGE statement for PostgreSQL informative. How do I do a "manual" ROLLBACK? To roll back to a particular savepoint: ROLLBACK TO SAVEPOINT spname; However, for what you're trying to do another option is to just issue a pair of statements that'll work anyway. You should probably test and see which works better, faster, etc. Assuming there's only one row in the table so I don't need any more specific WHERE clauses, I might try something like: UPDATE my_unique_table SET col = some_col_val; INSERT INTO my_unique_table ( col ) SELECT some_col_val WHERE NOT EXISTS (SELECT 1 FROM my_unique_table WHERE someotherconstraint = somevalue); because one of those two is guaranteed to work whether or not there are any rows in the table (assuming I got it all right, at least). Presumably you're doing something like limiting app instances to one per user, in which case you'd need additional constraints in the above (you wouldn't be keeping just one row anymore, but one per user) and some more indexes but the idea's basically the same. Maybe you should tell the readers of this list a little more about what you're trying to do and why? -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select into
On Mar 20, 2008, at 7:10 AM, Joe wrote: Gavin 'Beau' Baumanis wrote: The copy is inside the same table, so I don't understand why it (the required query ) would require any joins. Ie. I want to copy the contents of a row (but for the id column - of course) into a record in the same table. I think what you want is something like this: Given (col1 being the id or PK): col1 | col2 | col3 --+--+--- 1 | 123 | first record 2 | 456 | second record 3 | 789 | third record then update t1 set col2 = t1copy.col2, col3 = t1copy.col3 from t1 as t1copy where t1.col1 = 1 and t1copy.col1 = 3; will result in: col1 | col2 | col3 --+--+--- 1 | 789 | third record 2 | 456 | second record 3 | 789 | third record So, it is a join ... of a table with a virtual copy of itself. Note that in 8.2.x and above you can write that as: update t1 set (col2, col3) = (t1copy.col2, t1copy.col3) from t1 as t1copy where t1.col =1 and t1copy.col1=3; Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback locks table - why?
Hello Craig, > > Maybe you should tell the readers of this list a little more about what > you're trying to do and why? > -- ok, so I'll do that: I am programming a small economic Java simulation/serious game that has to calculate the GDP after 12 rounds. For doing this, I need the capital of each company at the very beginning of the game (i.e. runde (round) "0"). So when the user logs into the game, his/her client has to write a row in the the corresponding timeline table which holds the initial state of the company. For convinience reasons (since this will be used in laboratory conditions, only) I would just not want to purge the tables each time the game is restarted but would like to be sure, that the initial values are there for sure. The timeline can be dealt with via a timestamp that's also present. So basically that's that. Regards Jan -- GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen! Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback locks table - why?
"Jan Peters" <[EMAIL PROTECTED]> writes: > If I run the transaction block from above again I get first a unique > key violation (that is ok, because that's what I trying to check for) > but there is NO rollback to sp1, only the "Unique Key" error message > and after that I get the dreaded "current transaction is aborted" > error message and the system reports an EXCLUSIVE LOCK on the table > (in transaction). The example works when executed by hand: regression=# create table test(id int primary key, runde int); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test" CREATE TABLE regression=# INSERT INTO test(id,runde) VALUES(2, 0); INSERT 0 1 regression=# BEGIN; BEGIN regression=# SAVEPOINT sp1; SAVEPOINT regression=# INSERT INTO test(id,runde) VALUES(2, 0); ERROR: duplicate key value violates unique constraint "test_pkey" regression=# ROLLBACK TO sp1; ROLLBACK regression=# UPDATE test SET id = 1000 WHERE runde = 0; UPDATE 1 regression=# commit; COMMIT so the problem must be in something you didn't show us. What exactly are you doing to decide that you need to roll back? Also, none of these statements (except the CREATE) would take an exclusive lock on test, so there must be something else going on that you didn't show us. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback locks table - why?
Hello Tom, > so the problem must be in something you didn't show us. What exactly > are you doing to decide that you need to roll back? Also, none of these > statements (except the CREATE) would take an exclusive lock on test, so > there must be something else going on that you didn't show us. That is exactly what confuses me. I just put the above code in the SQL Query Editor of my Admin App (e.g. PGAdmin III) and click the run button, nothing else. First time I get "duplicate unique key" second time " ERROR: current transaction is aborted, commands ignored until end of transaction block"... Regards Jan -- GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen! Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback locks table - why?
Hm, Tom, your're right, it works in the console, but not in the editor window, strange... -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback locks table - why?
Yes, Tom, it looks like this: Locked Transaction:458553 I misread that. Sorry for that, I am actually a Geographer and just "using" the tools at hand. So I was not clear enough, it is a lock, but it's just the one of the transaction, right. That still does not explain why the Query Editor locks up, hm. I just tried to send the transaction to my servlet and it worked on the server es well. Seems the JDBC driver interprets the string differently. Well, if it's working on the server, I will not keep trying in the editor, although it might be nice, to have a solution. But as long as I use the console for testing purposes from now on, it's fine for me. Thanks again, Craig and Tom, if you still have an idea what might cause this behaviour, let me know. Otherwise: Happy Easter! Regards Jan -- GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen! Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compare 2 tables in sql
On Thu, Mar 20, 2008 at 1:44 PM, Tena Sakai <[EMAIL PROTECTED]> wrote: > Just a postscript. It is important to check > both ways. Because (sometimes) vice versa is > not necessarily true. Case in point below: Yes, I'm well aware of that. Still, you should UNION the result of both exceptions into a single result set. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837 | http://www.enterprisedb.com/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compare 2 tables in sql
Hi Jonah, > Still, you should UNION the result of > both exceptions into a single result set. Great suggestion. Many thanks. Regards, Tena Sakai [EMAIL PROTECTED] -Original Message- From: Jonah H. Harris [mailto:[EMAIL PROTECTED] Sent: Thu 3/20/2008 12:21 PM To: Tena Sakai Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] compare 2 tables in sql On Thu, Mar 20, 2008 at 1:44 PM, Tena Sakai <[EMAIL PROTECTED]> wrote: > Just a postscript. It is important to check > both ways. Because (sometimes) vice versa is > not necessarily true. Case in point below: Yes, I'm well aware of that. Still, you should UNION the result of both exceptions into a single result set. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837 | http://www.enterprisedb.com/
Re: [SQL] compare 2 tables in sql
Hi Jonah, Just a postscript. It is important to check both ways. Because (sometimes) vice versa is not necessarily true. Case in point below: blitzen=> select * from foo blitzen-> except blitzen->select * from moo; alleleid | markerid | value | datecreated | datereplaced --+--+---+-+-- (0 rows) blitzen=> blitzen=> select * from moo blitzen-> except blitzen->select * from foo; some_id | anothrid | value | datecreated |datereplaced --+--+---+-+- 2892473 | 2810329 | t | 2008-03-12 14:37:18.165 | 3000-01-01 12:00:00 (1 row) Regards, Tena Sakai [EMAIL PROTECTED] -Original Message- From: Jonah H. Harris [mailto:[EMAIL PROTECTED] Sent: Wed 3/19/2008 3:39 PM To: Tena Sakai Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] compare 2 tables in sql On Wed, Mar 19, 2008 at 1:56 PM, Tena Sakai <[EMAIL PROTECTED]> wrote: > Is there a sql way to compare (in a diff/cmp sense) > 2 tables? For example, SELECT * FROM foo EXCEPT SELECT * FROM moo; -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837 | http://www.enterprisedb.com/
[SQL] Dynamic sql and variable record types
Hi, I've been working on a generic date partitioning system and I
think I've reached something that can't be done, but I thought I'd
post a question to the masses in the hope that I'm missing something.
The basic idea of what I'm doing is some userland scripts that will
accept a table name argument along with the name of a date/timestamp
attribute to partition on and create partitions for that table along
with the appropriate trigger and trigger function. The part I'm
having trouble with is the trigger function.
What I'm done for that is to create a template file that my scripts
read in and substitute the table column names wherever necessary, then
run the results through the db to create the functions. The problem
is that for the function to be generic it needs to be able to work
with different record/row types. Here's the template for function
(not working, which I'll discuss below):
CREATE OR REPLACE FUNCTION %s_ins_func(op text, rec %s)
RETURNS boolean AS $$
DECLARE
partition varchar;
name_parts varchar[];
upper_dim integer;
BEGIN
FOR partition IN
SELECT relname
FROM pg_class
WHERE relname ~ ('^%s_[0-9]{8}_[0-9]{8}$')
LOOP
name_parts := string_to_array(partition, '_');
upper_dim := array_upper(name_parts, 1);
IF rec.%s >= name_parts[upper_dim-1]::timestamp AND rec.%s <
name_parts[upper_dim] THEN
IF op = 'INSERT' THEN
EXECUTE 'INSERT INTO %s_' || name_parts[upper_dim-1]
|| '_' ||
name_parts[upper_dim] || ' VALUES ' ||
rec || ';'; -- the problem is here with rec
RETURN TRUE;
END IF;
END IF;
END LOOP;
RETURN FALSE;
END;
$$ language plpgsql;
The userland scripts substitute the table and column names for the %s
escapes where appropriate. What the function actually does is to us
the parent table's name to find all of the child partitions which are
name like some_table_20080101_20080201, split out the dates from those
to determine which table the insert needs to be redirected to. That
works fine. The problem is that since I have to dynamically generate
the destination table name I have to use EXECUTE for the INSERT
statement. But, I can't see how to use a record in query passed to
EXECUTE. Am I right in thinking (now) that this can't be done?
Erik Jones
DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Date and filling issues
> > A sample of the current results data would be like > datesales > 2008-03-07 100.00 > 2007-03-10 150.00 > 2007-03-18 50.00 > > and what I'm trying to do is fill in the missing dates with sales > values of 0. what I do is have a table called days that is the left side of a left join. I'm not proud of this, but it works well. Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
