By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,780 Members | 1,259 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,780 IT Pros & Developers. It's quick & easy.

Insert with a serial

P: n/a
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.

I've done similar things in MS SQL server with stored procedures; you
return @@IDENTITY. I've also tried "SELECT SerialColumn FROM ( INSERT
INTO ...)" and that doesn't work. I suppose I could just do another
SELECT and get MAX(SerialColumn), but that's two queries.

Any ideas? Please reply to my email address.

Travis
Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
> 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().
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.