Inserting a row and related rows with autoincrement primary key together

Question

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

• image: which holds some basic meta data and has a autoincrement primary key id
• imagesize: which is holds multiple different sizes per image and there S3 url, width, height, etc. and has a foreign key to image.id So image forms a one-to-many relationship with imagesize.

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?

Answer 1

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:

• PostgreSQL store value returned by RETURNING
• INSERT rows into multiple tables in a single query, selecting from an involved table

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);

Answer 2

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