Search Postgresql Archives

Re: get inserted id from transaction - PG 9.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





2017-02-15 12:19 GMT+13:00 Tom Lane <tgl@xxxxxxxxxxxxx>:
Patrick B <patrickbakerbr@xxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux