Insert values if records don't already exist in Postgres

Question

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'
  );

Ester

Answer 1

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.-

Argosy

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? – Bernitabernj

@PascalvKooten because we need the WHERE to make our selection of values invalid if we can't find the key. – Argosy

@Bernitabernj The indentation might have been throwing you off: the WHERE clause belongs to the SELECT part of the statement, rather than the overall INSERT. – Jussive

Answer 2

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;

Frausto

how is that different than insert into where not exists... ? More safe when used concurrently? – Aerometry

@Aerometry 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 – Sharpen