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

An sqlfiddle would help.

Jakub Kania
– Jakub Kania

2014-02-13 20:57:11 +00:00
Commented Feb 13, 2014 at 20:57 — Jakub Kania
– Jakub Kania, Commented Feb 13, 2014 at 20:57

Jakub Kania · Accepted Answer · 2014-02-13 21:42:01Z

3

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

answered Feb 13, 2014 at 21:42

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gylaz Over a year ago

In, this case DISTINCT would make me lose the relation of sections that have matching file_urls. I still want to generate an attachments row in that case, using the existing media_files id.

Jakub Kania Over a year ago

@gylaz It won't, the sections that link to the same file (2,3) point to the same media_file as seen in the fiddle. So it works exactly as you describe it.

user330315 · Accepted Answer · 2014-02-13 21:07:49Z

3

I can only think of using an additional join in the outer insert:

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

answered Feb 13, 2014 at 21:07

user330315

3 Comments

user330315 Over a year ago

@JakubKania: yes, I was thinking about that. But the dupes would already be present in the original insert inot the media_files table and apparently that wasn't a problem (or gylaz didn't think about it)

gylaz Over a year ago

Ideally, if there is a duplicate file_url in sections -- I would want to create a media_files row the first time, and the second time I encounter it, fetch the existing row from media_files.

Jakub Kania Over a year ago

@a_horse_with_no_name Well, if we'd want one section - one media file and avoid the dupes we could join the tables on row number.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related