sql - 一起插入具有自增主键的行和相关行

Question

好吧，标题很烂，但这是我的问题。我有两个表:

• image:它包含一些基本的元数据并且有一个autoincrement 主键id
• imagesize:每个图像包含多个不同的尺寸，并且有 S3 url、宽度、高度等，并且有一个指向 image.id 的外键所以 image 与imagesize 形成一对多的关系。

目前，我首先使用 INSERT INTO image ... RETURNING image.id 创建图像，然后使用 INSERT INTO imagesize ... 创建尺寸，我使用返回的 id。

有没有办法将这两个 INSERT 结合起来，这样我的代码和数据库之间就没有额外的往返了？

Answer 1

使用 data-modifying CTE :

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

更多解释:

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

您的解决方案

CROSS JOIN 到一个 VALUES 表达式而不是多个 SELECT 语句:

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