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.
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.
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.