22

I'd like to get this working, but Postgres doesn't like having the WHERE clause in this type of insert.

  INSERT INTO keys(name, value) VALUES
    ('blah', 'true')
  WHERE NOT EXISTS (
    SELECT 1 FROM keys WHERE name='blah'
  );
Matt York
  • 15,981
  • 7
  • 47
  • 51

2 Answers2

67

In Postgres, there is a really nice way to do that:

INSERT INTO keys(name, value) 
    SELECT 'blah', 'true'
WHERE NOT EXISTS (
    SELECT 1 FROM keys WHERE name='blah'
);

hope that helps.-

VoidMain
  • 1,987
  • 20
  • 22
  • 6
    It beats me why suddenly we do not use VALUES here.... I've looked at so many places to find where people are using VALUES, but it seems "SELECT" is used instead. Super confusing? – PascalVKooten May 18 '16 at 08:42
  • @PascalvKooten because we need the ``WHERE`` to make our selection of values invalid if we can't find the key. – VoidMain May 18 '16 at 17:24
  • 4
    @PascalVKooten The indentation might have been throwing you off: the `WHERE` clause belongs to the `SELECT` part of the statement, rather than the overall `INSERT`. – Inkling Jan 17 '19 at 05:38
30

In Postgresql 9.5 you can now use on conflict do nothing if you also have a unique key or constraint:

insert into KEYS (name, value) values (
'blah', 'true') on conflict (name) do nothing;
Tiago
  • 1,984
  • 1
  • 21
  • 43
MK Yung
  • 4,344
  • 6
  • 30
  • 35
  • how is that different than `insert into where not exists...` ? More safe when used concurrently? – Konrad Feb 21 '19 at 13:45
  • 13
    @Konrad as far as I know, the `on conflict` clause only works on unique keys and constraints. for "normal columns", you should use the "where not exists". From the INSERT documentation on postgres: `Specifies which conflicts ON CONFLICT takes the alternative action on by choosing arbiter indexes. Either performs unique index inference, or names a constraint explicitly` – Murillo Ferreira Apr 12 '19 at 12:38