Getting an ID inside a PostgreSQL transaction block

Question

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.

user330315 · Accepted Answer · 2011-07-25 21:36:51Z

9

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;

answered Jul 25, 2011 at 21:36

user330315

Sign up to request clarification or add additional context in comments.

12 Comments

Steffen Over a year ago

Hm, both examples produce syntax error at or near "(" for me. Even if one of these will work eventually, I take it there is no way of using the value from album_id from my SELECT?

Tobi Akinyemi Over a year ago

Is lastval specific to the transaction, or could it be altered by another concurrent transaction?

user330315 Over a year ago

It's specific to the connection (=session)

Tobi Akinyemi Over a year ago

@a_horse_with_no_name so it could return the wrong value if another (sharing the connection) inserted concurrently

user330315 Over a year ago

@TobiAkinyemi: your assumption is wrong. Using lastval() directly after an INSERT is safe, just as using currval() is. If you have an application where it's possible that some code does an insert and another part does a different insert using the same physical connect before the first transaction gets to call lastval() then I would argue that your code is broken. But even in that situation currval() would be safe assuming that both inserts use different tables.

|

Tobi Akinyemi · Accepted Answer · 2020-10-16 08:55:17Z

3

Manually accessing sequences is not ideal

BEGIN;
  -- Create a new album row and hold onto the id
  WITH row as (INSERT INTO albums [...] RETURNING id AS album_id)
  -- Now use album_id in the next insert
  INSERT INTO album_images SELECT album_id, :image_id FROM row;
COMMIT;

returning was actually useful, however, the other 2 answers didn't know/explain how to apply it here

edited Oct 16, 2020 at 8:55

answered Oct 16, 2020 at 8:43

Tobi Akinyemi

9601 gold badge14 silver badges28 bronze badges

3 Comments

user330315 Over a year ago

"Manually accessing sequences is not a robust solution" - yes it is.

Tobi Akinyemi Over a year ago

@a_horse_with_no_name changed my verbiage

user330315 Over a year ago

You need to at least remove the ; after the CTE to make that a correct statement.

Cody Caughlan · Accepted Answer · 2011-07-25 21:32:51Z

1

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

edited Jul 25, 2011 at 21:32

answered Jul 25, 2011 at 21:24

Cody Caughlan

32.8k5 gold badges67 silver badges68 bronze badges

2 Comments

Steffen Over a year ago

Thanks, but my problem isn't so much with getting that ID, but with using it in the next statement. That SELECT in my example is actual working code that will return the correct ID. Or did I misunderstand something? Those examples are a little over my head.

Anthony Bobenrieth Over a year ago

When i try to use a named RETURNING inside another statement of the transaction it throw an "unknow column" errror... I dont think that thoses variables are shared within the transaction.

Pang · Accepted Answer · 2015-11-28 04:50:31Z

-2

You can get the id of the inserted row as

INSERT INTO albums [...] returning album_id;

This will return the album_id after insertion.

edited Nov 28, 2015 at 4:50

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Nov 28, 2015 at 4:42

HARI KRISHNAN

1

1 Comment

Jordan Arsenault Over a year ago

Specifically, the OP want's to use the id in the next statement in the transaction. RETURNING clause does not solve this independently.

Collectives™ on Stack Overflow

Getting an ID inside a PostgreSQL transaction block

4 Answers 4

12 Comments

3 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

12 Comments

3 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related