Insert into junction table after returning multiple id's

Question

I have a Postgresql database with three tables making up a many-to-many relationship, article, author and article_authors. I have a query based on this question which inserts to article and author if the record doesn't exist and returns the id, and if it does exist, it returns the id anyway:

WITH article_s AS (
    SELECT id 
    FROM article 
    WHERE id = 25
),
article_i AS (
    INSERT INTO article 
    (id, web_id, article_title, pub_date, publishers_id)
    SELECT 25, 'world/2013', 'a_title', '2015-07-07T21:58:28Z', 1
    WHERE NOT EXISTS (SELECT 1 FROM article_s)
    RETURNING id
),
author_s AS (
    SELECT id
    FROM author
    WHERE id = 34),
author_i AS(
    INSERT INTO author (id, name)
    SELECT 34, 'an_author'
    WHERE NOT EXISTS (select 1 from author_s)
    RETURNING id
)
SELECT article_i.id, author_i.id 
FROM article_i, author_i
UNION ALL
SELECT article_s.id, author_s.id 
FROM article_s, author_s;

I now need to insert the returned ID's into article_authors but not really sure how to access the returned ID's to do this. Is this possible, or is there a more elegant way of solving this problem? Thanks in advance!

score 2 · Accepted Answer · answered Jul 09 '15 at 23:21

[untested] I think you can use COALESCE() plus a carthesian product, since the _i and _s CTS are mutually exclusive:

WITH article_s AS (
    SELECT id
    FROM article
    WHERE id = 3
),
article_i AS(
    INSERT INTO article (id, web_id, article_title, pub_date, publishers_id)
    SELECT 3, 'world/2013', 'Queen hangs andrew', '2015-07-07T21:58:28Z', 1
    WHERE NOT EXISTS (SELECT 1 FROM article_s)
    RETURNING id
),
author_s AS (
    SELECT id
    FROM author
    WHERE id = 1
),
author_i AS (
    INSERT INTO author (id, name)
    SELECT 1, 'Bill Bryson'
    WHERE NOT EXISTS (select 1 from author_s)
    RETURNING id
)
INSERT INTO article_authors (article_id, author_id)
SELECT COALESCE(article_i.id, article_s.id)
, COALESCE(author_i.id , author_s.id)
FROM article_i, author_i 
  , article_s, author_s -- this is ugly
   ;

thanks for the response, only just really getting to grips with sql so i'll keep this in mind. — sammy88888888, Jul 10 '15 at 14:57

score 0 · Answer 2 · answered Jul 09 '15 at 23:04

For posterity, this is how I solved it:

WITH article_s AS (
    SELECT id
    FROM article
    WHERE id = 3
),
article_i AS(
    INSERT INTO article (id, web_id, article_title, pub_date, publishers_id)
    SELECT 3, 'world/2013', 'Queen hangs andrew', '2015-07-07T21:58:28Z', 1
    WHERE NOT EXISTS (SELECT 1 FROM article_s)
    RETURNING id
),
author_s AS (
    SELECT id
    FROM author
    WHERE id = 1
),
author_i AS (
    INSERT INTO author (id, name)
    SELECT 1, 'Bill Bryson'
    WHERE NOT EXISTS (select 1 from author_s)
    RETURNING id
)
INSERT INTO article_authors (article_id, author_id)
SELECT article_i.id, author_i.id 
FROM article_i, author_i
UNION ALL
SELECT article_s.id, author_s.id 
FROM article_s, author_s;

The `UNION` only handles the cases where **both** the author and the article were inserted or where **neither** of them were inserted. The two cases where only one of them was inserted are not covered (I hope the fields in the junction table are not NULLable). See the `COALESCE()` answer. — joop, Jul 10 '15 at 09:39
great, thanks for that, that's the one i've gone with in the end! — sammy88888888, Jul 10 '15 at 14:56

Insert into junction table after returning multiple id's

2 Answers2