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:

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.

Answers


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;

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


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.