Get data from the last insert query using postgres

Question

i know there is a way how i can get the whole data from my last insert, including the auto generated fields, like id, and default content. But the problem is: How can i do this?

for eg:

INSERT INTO schema.table (col1,col2) VALUES ("rowdata1","rowdata2");

the table looks like this:

id, col1, col2, col3 (default='t')

so how can i get the value of id and col3? There's a keyword like RETURNING or so, but this throws an error :)

Thanks for mentioning that it throws an error. It's definitely better than mere "it does not work", though it would be even more better if you posted the error message. — Quassnoi
– Quassnoi, Commented Nov 9, 2010 at 18:05

Quassnoi · Accepted Answer · 2010-11-09 18:04:07Z

4

INSERT
INTO    schema.table (col1, col2)
VALUES  ('rowdata1', 'rowdata2')
RETURNING
        *

, or, if you only need specific columns,

INSERT
INTO    schema.table (col1, col2)
VALUES  ('rowdata1', 'rowdata2')
RETURNING
        id, col3

answered Nov 9, 2010 at 18:04

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

therufa Over a year ago

thanks! for the fast answer. But theres one question open. Can i handle the returning data like i would handle it in a SELECT query?

Quassnoi Over a year ago

@Nort: if the query is all alone, yes. You cannot use it in a join, subquery etc., however, you can wrap it into a function and use the function wherever you would use a SELECT query.

Collectives™ on Stack Overflow

Get data from the last insert query using postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related