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.
http://www.postgresql.org/docs/8.3/interactive/sql-insert.html
Some examples: http://6c62.net/blog/?p=48
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With