Obtaining an identifier inside a PostgreSQL transaction block

Question

I'm trying to transfer all my transactions, which should be all or nothing, to BEGIN and COMMIT , but I'm not sure how to do this in the following cases.

I have 3 tables, one for images, one for albums and one for the relationship between them, namely album_images . The way the system works is that the user can create an album and fill it with his images in one operation. 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 look for, and I cannot find a solution in the documentation.

Answer 1

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

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

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

Another alternative - if you use only one insertion, is to use the lastval() function. Then you would not even need to put the sequence name in the INSERT statement:

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

Answer 2

Postgres provides the "RETURNING" clause used by INSERT to easily return the newly created primary key.

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

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

Answer 3

You can get the id of the inserted row as

 INSERT INTO albums [...] returning album_id; 

This will return album_id after insertion.