8

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;
11

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

7

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

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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