Thread: Does INSERT inserts always at the end ?
Hello, well, almost everything is in the subject ! I have to fill 2 tables (more complicated than in the example !): CREATE TABLE A ( id serial primary key, foo text); CREATE TABLE B ( id serial references A, bar text); I fill A with : INSERT into A VALUES (DEFAULT, "toto"); Then I need to retreive the "A.id" that was given to A, in order to give it to B.id. If I was doing this by hand, it would have been quite easy, but I'm doing this with a script. So, if I make a SELECT id from A; and take the last row, will it *always* be the row that I've just inserted. Thanks for any tip -- Florence HENRY
florence.henry@obspm.fr (Florence HENRY) wrote: > > Hello, > > well, almost everything is in the subject ! > > I have to fill 2 tables (more complicated than in the example !): > > CREATE TABLE A ( > id serial primary key, > foo text); > > CREATE TABLE B ( > id serial references A, > bar text); > > I fill A with : > INSERT into A VALUES (DEFAULT, "toto"); > > Then I need to retreive the "A.id" that was given to A, in order to give it > to B.id. If I was doing this by hand, it would have been quite easy, but I'm > doing this with a script. Define what you mean by "with a script." If you've a db handle open with, say, Perl's DBI, you could simply do a select on currval() for the sequence and get it. This is immune to other transactions. But if, by "script" you mean, say, from a shell script, where you're feeding commands to psql from stdin or some-such, well... I suppose you could "echo 'mumble; select currval(blurfl)' |psql" and capture it. (Caveat: I haven't tried this. I'm just guessing.) > > So, if I make a SELECT id from A; and take the last row, will it *always* > be the row that I've just inserted. I was told just a few days ago to always regard data in a table as "unordered." Going by that philosophy: No, you cannot. You *certainly* cannot if more than one session/task is operating on the table. Jim
jseymour@LinxNet.com (Jim Seymour) wrote : > Define what you mean by "with a script." If you've a db handle open > with, say, Perl's DBI, you could simply do a select on currval() for > the sequence and get it. This is immune to other transactions. Indeed, I'm using Perl's DBI. currval does exactly what I want. Thanks to all who pointed it to me. Before I could read your messages, I made a workaround that worked nice also, but could you tell me if it would always work ? It uses the "oid" number. I've read that it used to be unique, but within the same session, I can assume that it is always growing, can't I ? INSERT into A VALUES (DEFAULT, "toto"); INSERT into B VALUES ( (SELECT id FROM A WHERE oid = SELECT ( max(oid) from A)), "toto"); -- Florence HENRY florence point henry arobasse obspm point fr
On Tue, May 25, 2004 at 01:15:14AM -0700, Florence HENRY wrote: > Indeed, I'm using Perl's DBI. currval does exactly what I want. Thanks to > all who pointed it to me. That's the way it should be done. > Before I could read your messages, I made a workaround that worked nice also, > but could you tell me if it would always work ? > > It uses the "oid" number. I've read that it used to be unique, but within > the same session, I can assume that it is always growing, can't I ? NO! The OID will wrap eventually, it is not guarenteed to be unique. And you can now make tables without OIDs. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.