PostgreSQL insert into multiple tables, using foreign key from first insertion in the second insertion

postgresql, sql

Solution

If you want to avoid problems with duplicates you should use a `DISTINCT` clause in the first `INSERT`. This will make two sections point to the same media_file but it should be more than okay. (Upgraded from @a_horse_with_no_name answer)

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

Problem

I'm trying to take an existing table and create two. I have a `sections` table that has a `file_url`, and now I need to move that into `url` field on `media_files` table. Also, I want to populate `attachments` table that acts as a `has_many :through` (in Rails) which means it keeps track of the foreign key relationships of `media_file_id` and `section_id`. The closest that I've gotten is this: ``` 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 ``` but I'm getting this error: ``` PG::UndefinedTable: ERROR: missing FROM-clause entry for table "sections" LINE 6: RETURNING id AS media_file_id, sections.id AS sectio... ``` which makes sense, but I'm not sure how to grab the `section_id`. Is there a way to get this working? Maybe there's a better, alternative method? Edit: Here's a link to SQL Fiddle

Original source