2017-02-15 12:19 GMT+13:00 Tom Lane <t...@sss.pgh.pa.us>: > Patrick B <patrickbake...@gmail.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 >
