How do I get the value of the autogenerated fields using Postgresql?

Question

I have a postgres table like this:

CREATE SEQUENCE seq;
CREATE TABLE tbl (id INTEGER DEFAULT VALUE nextval('seq'), data VARCHAR);

When I insert into the table:

INSERT INTO tbl (data) VALUES ('something');

How can I get back the value of the id field for the record I just created?

(Note, I may have got some of the SQL syntax a bit off; the gist should be clear, though)

Suppose for the sake of argument that I'm not able to call currval on the same session because I don't control the transaction boundaries. I might be working in the same session with my next call, but I might not be, too.

Answer 1

Use nextval and currval, take a look here: http://www.postgresql.org/docs/8.0/static/functions-sequence.html

Answer 2

In the same session:

SELECT currval('seq');

EDIT: But if you can't use currval/nextval because you don't know if the inserting and selecting of the sequence value will occur in the same session, and if you're on postgresql 8.2 or later, you could probably write your insert statement like this.

INSERT INTO tbl (data) VALUES ('something') RETURNING id;

which should also return the last inserted id.

Answer 3

You're looking for INSERT ... RETURNING. From The Manual's section on INSERT:

The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted. This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed. The syntax of the RETURNING list is identical to that of the output list of SELECT.