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

Question

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

Answer 1

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