Re: get inserted id from transaction - PG 9.2

On Tue, Feb 14, 2017 at 3:55 PM, Patrick B <patrickbakerbr(at)gmail(dot)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.
​