Getting an ID inside a PostgreSQL transaction block

Question

I'm trying to wrap all my transactions that should be all-or-nothing into BEGIN and COMMIT, but I'm not sure how to do this in cases like the following.

I have 3 tables, one for images, one for albums, and one for the relations between them, namely album_images. The way the system works is that a user can create an album and fill it with his images in one operation. The SQL is as follows:

BEGIN;
  INSERT INTO albums [...];  -- Create a new album row
  SELECT id AS album_id FROM albums WHERE [...];  -- Get that rows ID
  -- Now use album_id in the next statement
  INSERT INTO album_images (album_id, image_id) [...];
COMMIT;

This is probably a common problem, I'm just not sure what to search for and I can't seem to find a solution in the documentation either.

Answer 1

As an alternative to the INSERT ... RETURNING clause mentioned by Cody, you can use the current value the sequence associated with the ID column:

BEGIN;
  INSERT INTO albums [...];
  INSERT INTO album_images (currval('albums_id_seq'), image_id) [...];
COMMIT;

This assumes the standard naming scheme from Postgres when creating the sequence automatically for columns defined as serial.

Another alternative - if you are only using a single insert - is to use the lastval() function. You would then not even need to put the sequence name into the INSERT statement:

BEGIN;
  INSERT INTO albums [...];
  INSERT INTO album_images (lastval(), image_id) [...];
COMMIT;

Answer 2

Postgres provides a "RETURNING" clause used in an INSERT to easily get back the newly-created primary key.

http://www.postgresql.org/docs/8.3/interactive/sql-insert.html

Some examples: http://6c62.net/blog/?p=48