Get value of newly-inserted row's SERIAL col in PostgreSQL
Posted: Tue Mar 21, 2006 5:24 pm
I created a PostgreSQL table using implicit sequence creation, like so:
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.
This will return:
Unfortunately, the implied pattern [tablename]_[columnname]_seq doesn't seem to apply to my situation.
Code: Select all
CREATE TABLE tSomething(ID SERIAL, col1 type, col2 type...);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;Code: Select all
CREATE TABLE will create implicit sequence "test_id_seq" for "serial" column "test.id"