How can I insert a row only if it does't already exist?

Question

I'm trying to insert a row but only if it is not already present in the table. At the moment I'm using code similar to the following:

insert into mentions (project_id, id, data_source, channel)
  select 3, '123456789', 'example.com', 'twitter'
  where not exists
    (select 1 as "one" from mentions where (project_id = 3 and id = '123456789'))
returning reach;

But I sometimes see it fail with the following error in the logs

ERROR: duplicate key value violates unique constraint "mentions_pkey"

DETAIL: Key (project_id, id)=(3, 123456789) already exists.

and the constraint is defined as

Index "public.mentions_pkey"
┌────────────┬─────────┬────────────┐
│  Column    │  Type   │ Definition │
├────────────┼─────────┼────────────┤
│ project_id │ integer │ project_id │
│ id         │ text    │ id         │
└────────────┴─────────┴────────────┘
primary key, btree, for table "public.mentions"

Since I'm only inserting when the exists clause is false, I don't understand how this can ever fail with a constraint violation.

Could it be a concurrency issue? I was under the assumption that individual statements are atomic but maybe this isn't the case?

I know that once 9.5 ships I can use ON CONFLICT DO NOTHING, but I'm running 9.4 at the moment.

That question was asked when most recent version of Postgresql hadn't on conflict clause. Notice that op specified version after the first two questions were post, too. — Roberto, Nov 27 '15 at 19:02

score 1 · Answer 1 · answered Nov 27 '15 at 18:48

1

use ON CONFLICT optional clause on insert:

insert into mentions  (project_id, id, data_source, channel)
  values (3, '123456789', 'example.com', 'twitter')
  ON CONFLICT (project_id, id) DO NOTHING;

look: http://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT

answered Nov 27 '15 at 18:48

Roberto

2,115
24
32

9.5 hasn't been released yet, it's still in beta. – Ian Phillips Nov 30 '15 at 12:09

score 0 · Accepted Answer · answered Nov 27 '15 at 18:43

handle errors in stored procedure

  CREATE OR REPLACE FUNCTION some_insert() RETURNS VOID AS
  $$BEGIN
    BEGIN
      insert into mentions (project_id, id, data_source, channel)
            select 3, '123456789', 'example.com', 'twitter';
      RETURN;
    EXCEPTION WHEN unique_violation THEN
      -- do nothing
    END;
  END;$$
  LANGUAGE plpgsql;

lock table

BEGIN;
LOCK TABLE mentions IN SHARE ROW EXCLUSIVE MODE;
INSERT INTO mentions (project_id, id, data_source, channel)
    SELECT 3, '123456789', 'example.com', 'twitter'
    WHERE NOT EXISTS
        (SELECT 1 AS "one" FROM mentions WHERE (project_id = 3 AND id = '123456789'))
COMMIT;

handle errors on postgres' client side

Yeah, I think that I'm going to have to go the stored procedure version, was hoping to avoid that :-( — Ian Phillips, Nov 30 '15 at 12:10

How can I insert a row only if it does't already exist?

2 Answers2