CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expressionsql-insert
Martin Thurau
Martin Thurau

Reputation: 7644

Inserting a row and related rows with autoincrement primary key together

Okay, so the title sucks but here is my problem. I have two tables:

Currently I first create and image with INSERT INTO image ... RETURNING image.id and then create the sizes with INSERT INTO imagesize ... where I use the returned id.

Is there a way of combining these two INSERTs so there is no additional roundtrip between my code and the database?

Upvotes: 3

Views: 959

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

Use a data-modifying CTE:

WITH ins1 AS (
   INSERT INTO image  ...
   RETURNING id
   )
INSERT INTO imagesize (..., image_id)
SELECT ..., i.id
FROM   ins1 i;

More explanation:

Your solution

CROSS JOIN to a VALUES expression instead of multiple SELECT statements:

WITH new_img AS (
   INSERT INTO image (description)
   VALUES ('An awesome image')
   RETURNING id
   )
INSERT INTO imagesize (image_id, name, url) 
SELECT i.id, v.name, v.url
FROM   new_img i
CROSS  JOIN (
   VALUES
     ('small' , 'http://example.com/img_2_small.jpg')
    ,('medium', 'http://example.com/img_2_medium.jpg')
    ,('large' , 'http://example.com/img_2_large.jpg')
   ) v (name, url);

Upvotes: 2

Martin Thurau
Martin Thurau

Reputation: 7644

Erwin Brandstetter pointed me in the right direction. Since I need to insert multiple rows, the query looks a bit strange, but it works:

WITH new_img AS (
  INSERT INTO image (description) VALUES ('An awesome image') RETURNING id
)
INSERT INTO imagesize (image_id, name, url) 
SELECT           new_img.id, 'small', 'http://example.com/img_2_small.jpg' FROM new_img
UNION ALL SELECT new_img.id, 'medium', 'http://example.com/img_2_medium.jpg' FROM new_img
UNION ALL SELECT new_img.id, 'large', 'http://example.com/img_2_large.jpg' FROM new_img
;

Here is a SQL Fiddle

Upvotes: 1

Related Questions