PostgreSQL插入多个表，使用第一次插入的外键在第二次插入中。

Question

我正在尝试将一个现有的表格分成两个。我有一个名为sections的表格，其中包含一个file_url字段，现在我需要将其移动到media_files表格的url字段中。此外，我想要填充attachments表格，它作为一个has_many :through（在Rails中），这意味着它会跟踪media_file_id和section_id之间的外键关系。

我最接近的方法是这样的：

WITH ids AS (
    INSERT INTO media_files (url, mimetype)
    SELECT file_url, 'image/jpeg'
    FROM sections
    WHERE file_url IS NOT NULL
    RETURNING id AS media_file_id, sections.id AS section_id
)
INSERT INTO attachments (media_file_id, section_id)
SELECT media_file_id, section_id
FROM ids

但是我遇到了这个错误：

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "sections"
LINE 6:         RETURNING id AS media_file_id, sections.id AS sectio...

这是有道理的，但我不确定如何获取section_id。

有没有方法可以让它工作？也许有更好的替代方法？

编辑： 这里是SQL Fiddle链接

- gylaz

---

一个 SQLFiddle 会很有帮助。 - Jakub Kania

Answer 1

我只能想到在外部插入中使用额外的连接：

WITH ids AS (
    INSERT INTO media_files (url, mimetype)
    SELECT file_url, 'image/jpeg'
    FROM sections
    WHERE file_url IS NOT NULL
    RETURNING id AS media_file_id, url AS file_url
)
INSERT INTO attachments (media_file_id, section_id)
SELECT ids.media_file_id, s.id
FROM ids
  JOIN sections s ON s.file_url = ids.file_url;

SQLFiddle: http://sqlfiddle.com/#!15/6fcaf/4

- user330315

---

@JakubKania：是的，我在考虑这个问题。但是重复数据已经存在于media_files表的原始插入中了，显然这并不是一个问题（或者gylaz没有考虑到它）。 - user330315

理想情况下，如果在“sections”中存在重复的“file_url”，我希望第一次创建“media_files”行，而在第二次遇到它时，从“media_files”中获取现有的行。 - gylaz

@a_horse_with_no_name 好的，如果我们想要一个部分 - 一个媒体文件并避免重复，我们可以在行号上连接表格。 - Jakub Kania

Answer 2

如果您想避免重复问题，应在第一个INSERT中使用DISTINCT子句。这将使两个部分指向相同的media_file，但这应该是完全没问题的。（升级自@a_horse_with_no_name的答案）

WITH ids AS (
    INSERT INTO media_files (url, mimetype)
    SELECT DISTINCT file_url, 'image/jpeg'
    FROM sections
    WHERE file_url IS NOT NULL
    RETURNING id AS media_file_id, url AS file_url
)
INSERT INTO attachments (media_file_id, section_id)
SELECT ids.media_file_id, s.id
FROM ids
  JOIN sections s ON s.file_url = ids.file_url;

Fiddle

- Jakub Kania

---

在这种情况下，使用DISTINCT会使我失去具有匹配的file_url的sections之间的关系。但我仍希望在这种情况下生成一个attachments行，使用现有的media_files id。 - gylaz

@gylaz 不会的，链接到同一文件（2,3）的部分指向与fiddle中看到的相同的media_file。因此它的工作方式与您描述的完全相同。 - Jakub Kania