[Libpqxx-general] Primary key

[Robert Backhaus-pqxx]

On Sat, Nov 7, 2009 at 6:11 AM, Jean-Michel Personne <jmc9497 at gmail.com>wrote:

> Hello,
>
> I'm new with pqxx and I've a little question. After a INSERT statement, how
> can I get the primary key for the new line ?
>
> Thanks.
>
> There are two options, depending on which version postgresql you are using.
One, the best, if you have a posgresql server version 8.2 or later, is
INSERT...RETURNING, as Trigve stated.

INSERT INTO table (data) VALUES ("information") RETURNING id;

For older databases, there are many options, but this is the best one. It
queries the sequence for the value that was just used for the recent insert.
It is safe, because each connection makes sure its sequences are consistent
- even if another thread, program or user does an insert, currval() will
return the right id.

INSERT INTO table (data) VALUES ("information"); SELECT
currval('public.table_id_seq') as id;

You can put both queries in the same exec(), or can seperate them into two
exec()'s - the first is more convenient and slightly faster, the second is
probably better, as it allows the system to throw more meaningful
exceptions.

Of course, only use this method if you cannot upgrade to POSTGRES 8.2 or
later.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/libpqxx-general/attachments/20091107/37a3ec57/attachment.html>