Getting an ID inside a PostgreSQL transaction block

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:

  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) [...];

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.


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

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

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:

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

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

Some examples:

You can get the id of the inserted row as

INSERT INTO albums [...] returning album_id;

This will return the album_id after insertion.

Need Your Help

Extract ID from the JSON Response in Linux

linux json sed grep extract

Here is my json response from the server.This response i am saving in a variable named TESTDEMO.