Page 1 of 1

Get value of newly-inserted row's SERIAL col in PostgreSQL

Posted: Tue Mar 21, 2006 5:24 pm
by tomprogers
I created a PostgreSQL table using implicit sequence creation, like so:

Code: Select all

CREATE TABLE tSomething(ID SERIAL, col1 type, col2 type...);
Now I want to grab the ID of a record I just inserted. I've found some sample code elsewhere on this forum, using the CURRVAL() function. However, CURRVAL takes as its argument the name of the sequence that was created. Since I relied on the SERIAL type to establish the sequence for me, I don't know its name, and I don't know how to discover it.

If anyone has any suggestions for ways to get the name of the serial column's sequence, or has a more elegant way of retrieving the value without using an actual query (perhaps a pg_* function I'm not aware of), I'd very much appreciate it.

I'm using PostgreSQL 7.1.4.

Edit: I ran across the following information on the PHP.net page for the pg_last_notice function.

Code: Select all

$res = pg_query("CREATE TABLE test (id SERIAL)");

$notice = pg_last_notice($pgsql_conn);

echo $notice;
This will return:

Code: Select all

CREATE TABLE will create implicit sequence "test_id_seq" for "serial" column "test.id"
Unfortunately, the implied pattern [tablename]_[columnname]_seq doesn't seem to apply to my situation.