ID of the last inserted row in PgSQL

Hi…

How do I retrieve the ID of the last inserted/updated row in PgSQL? MySQL has mysql_insert_id() But PgSQL’s pg_last_oid() doesn’t return the right number. I’ve read that I should use pg_get_serial_sequence, which is a native PgSQL function, but I don’t know how :frowning:
I’m running PgSQL 8.0.1 btw

Here is what I use in Postgres:

$strGetVal = ‘SELECT currval(\’“tblname_id_seq”\‘) AS “retVal”’;

PgSQL complains about “currval of sequence ‘tblname_id_seq’ is not yet defined in this session”, where tblname is of course the name of an existing table in the database. What should I do?

Do you have a sequnce set up for your serial? Is your id column a serial even??

Can you show us your table structure?

Here is how I’ve created the table:


CREATE TABLE articles
(
  id            serial,
  forum_post_id int         not null,
  author        varchar(24) not null,
  title         varchar(64) not null,
  date          date        not null,
  summary       text,
  body          text        not null,
  status        char(7)     check (status in ('Private', 'Public'))
);

can you connect to your db and do a \d and make sure your sequence is named appropriately.

also try:

SELECT last_value FROM tblName_id_seq;

Yes, it is named ‘articles_id_seq’. I’m issuing the following SQL-query directly to the database: SELECT currval(‘articles_id_seq’); it still complains about “currval of sequence ‘tblname_id_seq’ is not yet defined in this session”. BUT if I execute the following query first: SELECT nextval(‘articles_id_seq’); and then SELECT currval(‘articles_id_seq’); … it works…for some unknown reason.

Anyhow I think I’m going to ditch this method althogether since it doesn’t work as expected AT ALL. Instead, I’ll do a SELECT MAX(id) FROM tblname AS currVal …dunno if it will work in all cases…any comments?

also try:

SELECT last_value FROM tblName_id_seq;

Thanks! This worked a lot better :slight_smile:

Glad you got it. Weird that currval() didn’t work… but this is programming after all :stuck_out_tongue:

Sequences are session based (as in connections). currval() will return the last ID generated from a nextval(), which is why you SELECT currval(‘foo’) after you do INSERT INTO foo;