getting id in postgres

[dast]

I need to get the id of the record just added in POstgres, any ideas?

Thanks in advance

[Sxooter]

Sure. Is this from a serial field???

You can do it like this:

begin;
insert into table values (DEFAULT, val1,val2);
select currval('seqname');
insert into dep_table values (id_fromabove,vala,valb);
commit;

where DEFAULT is in the position of your serial field (DEFAULT was introduced in 7.3, by the way) If you're on 7.2. or before, you have to list the field names:

insert into table (field1,field2) values (val1, val2);

OR

begin;
select nextval('seqname');
insert into table (id, field1, field2) values (id_fromabove,val1, val2);
... rest the same as above.

The docs cover this pretty well actually, look in the section under the user docs / functions / sequence functions.

[dast]

Thanks for your reply. OK I've got the sequences set up already, I'm actually looking to do this in PHP rather than in postgres itself. Im basically adding a record and am needing the id of that record straight after.

[Sxooter]

Cool, then just wrap all those statements into pg_query() s and grab the value you get back from currval / nextval and you're set.

Definitely read up on those functions, as their operation is mildly counter-intuitive at first (for good reason, I might add.)

for instance currval isn't set until setval or nextval have been called in the same transaction.

[trooper]

No need for the previous post
just use the "pg_last_oid()" function

HTH

GM

[Sxooter]

Unless you created your tables WITHOUT OIDs, and as OIDs are likely going away in a year or so (by default I should say, they'll still be there, just not by default) it's a poor choice to use pg_last_oid()...