Postgres 9.4 -- How do I ignore duplicate key when inserting rows into a table

Question

I have a story_id table like this.

    Column    |  Type   |                             Modifiers
--------------+---------+-------------------------------------------------------------------
 id           | integer | not null default nextval('story_sites_id_seq'::regclass)
 story_id     | integer | not null
 site_id      | integer | not null

I want to add new rows with site_id = 12 and story_id which already has site_id = 70 , here is my query, it works well:

INSERT INTO story_sites (story_id, site_id)
SELECT story_id, 12
FROM story_sites
WHERE site_id = 70;

But if there're rows which already has site_id = 12, I'll get a duplicate key error.

So I want to exclude those duplicate rows, here is my new query, however it doesn't work properly, it inserts nothing when I think it should. Also it seems I can't use ON CONFLICT DO NOTHING in Postgres 9.4. How should I do it ?

INSERT INTO story_sites (story_id, site_id)
SELECT story_id, 12
FROM story_sites
WHERE site_id = 70
AND NOT EXISTS
(
    SELECT id
    FROM story_sites
    WHERE site_id = 12
);

Gordon Linoff · Accepted Answer · 2018-12-07 02:15:39Z

3

You are close. You just need a to take the story_id into account:

INSERT INTO story_sites (story_id, site_id)
    SELECT ss.story_id, 12
    FROM story_sites ss
    WHERE ss.site_id = 70 AND
          NOT EXISTS (SELECT id
                      FROM story_sites ss2
                      WHERE ss2.site_id = 12 AND ss2.sotry_id = ss.story_id
                     );

All that said, this query still has the potential for having problems if multiple inserts are happening at the same time. What you really should be using is on conflict do nothing, which is explained in the documentation for insert.

answered Dec 7, 2018 at 2:15

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Comments

Zon · Accepted Answer · 2019-05-08 06:32:15Z

-1

Move up from version 9.4 to 9.5 Do not make subrequests. It can lead to decreases in performance. Since Postgres 9.5 you can simply use:

INSERT INTO my_table (id, name) VALUES (1, 'Me') 
ON CONFLICT DO NOTHING;

edited May 8, 2019 at 6:32

answered Mar 20, 2019 at 15:58

Zon

20k8 gold badges99 silver badges104 bronze badges

1 Comment

Jack Stouffer Over a year ago

This will not work in 9.4, which the question is specifically about.

Collectives™ on Stack Overflow

Postgres 9.4 -- How do I ignore duplicate key when inserting rows into a table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related