Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Assuming table "entity.eid" is auto incrementing, I want to be able to reference the autoincrement value assigned later in the same transaction. The way I have been doing this is by doing multiple transactions which I think is not optimal.

START TRANSACTION;
INSERT INTO entity ...;
INSERT INTO t2 (eid, ...) VALUES (?NEW EID REF HERE?, ...), (...), (...);
COMMIT;
share|improve this question

3 Answers 3

up vote 2 down vote accepted

There are different ways to do this.

The easiest way is to use the lastval() function which will return the value generated by the "last" sequence nextval.

START TRANSACTION;
INSERT INTO entity ...;
INSERT INTO t2 (eid, ...) VALUES (lastval(), ...), (...), (...);
COMMIT;

If you know the name of the sequence for the entity table you could also use the currval function:

START TRANSACTION;
INSERT INTO entity ...;
INSERT INTO t2 (eid, ...) VALUES (currval('entity_eid_seq'), ...), (...), (...);
COMMIT;

This can be written in a more general way by using the pg_get_serial_sequence() function, avoiding to hardcode the sequence name:

START TRANSACTION;
INSERT INTO entity ...;
INSERT INTO t2 (eid, ...) VALUES (currval(pg_get_serial_sequence('entity', 'eid')), ...), (...);
COMMIT;

For more details, please see the manual: http://www.postgresql.org/docs/current/static/functions-sequence.html

share|improve this answer

You don't specify your Postgresql version, but if you are using 8.4+ you can use the RETURNING clause to return the id (or any column) that just got inserted.

Docs: http://www.postgresql.org/docs/current/static/sql-insert.html

Example:

INSERT INTO t2 (eid, ...) VALUES (...) RETURNING eid;

If you are using Postgresql version 9.1+ you can also use WITH clauses (aka Common Table Expressions) to do the insert into one clause, then reference the values from the RETURNING clause to perform more actions (the WITH clauses can chain together).

Docs on WITH clause: http://www.postgresql.org/docs/current/static/queries-with.html

share|improve this answer

It's worth noting that SERIAL in Postgres is just an INT with a SEQUENCE as the default value, you could easily query the sequencer yourself in a transaction instead of inserting into the table allowing the default to happen.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.