sql - 使用返回的 id 插入多对多关系表

Question

拿下面的表格

CREATE TABLE album(
  id SERIAL PRIMARY KEY NOT NULL,
  duration INTEGER NOT NULL
);

CREATE TABLE genre(
  id SERIAL PRIMARY KEY NOT NULL,
  name VARCHAR(32) NOT NULL
);

CREATE TABLE album_genre(
  album_id REFERENCES album(id),
  genre_id REFERENCES genre(id)
);

我有以下数据要插入

const album = {
  duration: 12345
  genre1Id: 1,
  genre2Id: 2,
  genre3Id: 3
}

我如何构造一个 SQL 查询来插入专辑数据，然后使用流派 ID 和插入专辑的 ID 插入 album_genre

Answer 1

在 Postgres 中，您可以在 CTE 中使用 inserts 构造查询。以下适用于您描述的情况:

with data(duration, genre1Id, genre2Id, genre3Id) as (
      values(12345, 1, 2, 3)
     ),
     i as (
      insert into albums(duration)
          select duration
          from data
          returning *
     )
insert into album_genre (album_id, genre_id)
    select i.id, v.genre
    from i join
         data d
         on i.duration = d.duration, lateral
         (values (d.genre1Id), (d.genre2Id), (d.genre3Id)) v(genre);

对于多行，相同的构造有效，但假定 duration 是唯一的。您需要一个唯一标识符来将插入的行与原始数据相匹配。