Re: get inserted id from transaction - PG 9.2

2017-02-15 12:19 GMT+13:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Patrick B <patrickbakerbr(at)gmail(dot)com> writes:
> > I'm simply doing an insert and I want to get the inserted id with a
> select.
> > I'm doing this all in the same transactions.
>
> > Example:
> > BEGIN;
> > INSERT INTO test (id,name,description) VALUES (default,'test 1','testing
> > insert');
> > SELECT FROM test ORDER BY id DESC; -- I don't see the inserted row here
>
> Maybe you meant "SELECT * FROM test", or at least "SELECT id FROM test"?
> Because that row certainly should be visible here.
>
> Having said that, the above coding seems rather broken, because it's just
> assuming that the new row will have the highest ID in the table. Even if
> that's true at the instant of insertion, you have a race condition:
> another transaction could insert and commit a new row with a higher ID
> between your INSERT and your SELECT.
>
> The usual solution for this problem in PG is RETURNING:
>
> INSERT INTO test (id,name,description)
> VALUES (default,'test 1','testing insert')
> RETURNING id;
>
>
Thanks guys!

RETURNING id - it's what i was looking for.

Thanks a lot!
Patrick

> That will get you the generated column's value reliably, and it avoids
> one query roundtrip besides.
>
> regards, tom lane
>