> Hi,
I've got a table in PostgreSql 7.3.2 that has a serial column as a
primary key. I want to insert data into the table (inserting values for
everything EXCEPT the serial column) and at the same time get the value
that the serial column just got incremented to. I'm using PHP for my
API, and pg_query() doesn't return anything on an insert. pg_insert()
just returns a bool.
Any ideas? Please reply to my email address.
FAQ 4.15.2 : How do I get the value of a SERIAL insert?
One approach is to retrieve the next SERIAL value from the sequence object
with the nextval() function before inserting and then insert it
explicitly. Using the example table in 4.15.1, an example in a
pseudo-language would look like this:
new_id = execute("SELECT nextval('person_id_seq')");
execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')");
You would then also have the new value stored in new_id for use in other
queries (e.g., as a foreign key to the person table). Note that the name
of the automatically created SEQUENCE object will be named
<table>_<serialcolumn>_seq, where table and serialcolumn are the names of
your table and your SERIAL column, respectively.
Alternatively, you could retrieve the assigned SERIAL value with the
currval() function after it was inserted by default, e.g.,
execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
new_id = execute("SELECT currval('person_id_seq')");
Finally, you could use the OID returned from the INSERT statement to look
up the default value, though this is probably the least portable approach,
and the oid value will wrap around when it reaches 4 billion. In Perl,
using DBI with Edmund Mergl's DBD::Pg module, the oid value is made
available via $sth->{pg_oid_status} after $sth->execute().
