ID of the last inserted row in PgSQL


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:

  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;