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'
);
Add a comment
|
2 Answers
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.-
3 Comments
PascalVKooten
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?
VoidMain
@PascalvKooten because we need the
WHERE to make our selection of values invalid if we can't find the key.
Inkling
@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.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;
2 Comments
Konrad
how is that different than
insert into where not exists... ? More safe when used concurrently?
Murillo Ferreira
@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