get inserted id from transaction - PG 9.2

On Tue, Feb 14, 2017 at 3:55 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

Hi all,

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

SELECT <what?> FROM test ...

Written correctly it w
orks for me...

CREATE TABLE testserial (id serial PRIMARY KEY);
BEGIN;
INSERT INTO testserial VALUES (DEFAULT);
SELECT * FROM testserial;

I see one row with id = 1 ...

SELECT version();
version
PostgreSQL 9.3.12 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit

​Default transaction isolation level.​​

COMMIT;

I only can see that inserted row if I do the select outside of this
transaction.

How could I get that ?

The easiest solution is:

INSERT INTO test [...]
RETURNING id;

David J.
​

On Feb 14, 2017, at 2:55 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

Hi all,

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

You want "select * from test ..." or "select id from test ..." here. Should work fine then.

COMMIT;

I only can see that inserted row if I do the select outside of this transaction.

How could I get that ?

This'd be the idiomatic way of doing it:

INSERT INTO test (name,description) VALUES ('test 1','testing insert') RETURNING id;

Cheers,
Steve

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Patrick B <patrickbakerbr@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;

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

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2017-02-15 12:19 GMT+13:00 Tom Lane <tgl@sss.pgh.pa.us>:

Patrick B <patrickbakerbr@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