Insert, if unique constraint violation then update; or update, if not found then insert.
in PostgreSQL 9.5
Alexandr Bezenkov commented
Thanks, people :)))
Bravo! And thankyou!
Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE
Matthew Krauss commented
This behavior is easily acheived with rules. If you do not want to change the standard insert/update behavior on a table, you can place the rule on a view instead. That is not to say that an explicit merge statement wouldn't be nice... just that it isn't all that urgent.
Rick Basset commented
The problem is that has a race condition where the row does not exist yet, that can cause the insert to fail for concurrent executors and people's usual expectation is that upserts "can't fail".
Davi Koscianski Vidal commented
And what, exactly, is the problem with
WITH upsert AS (
UPDATE tbl SET foo = 42 RETURNING *
INSERT INTO tbl(foo) VALUES(42) WHERE tbl.id NOT IN (SELECT id FROM upsert);
Darren Duncan commented
Either use the SQL standard syntax or invent something superior please, rather than say copying a proprietary syntax of some other DBMS.
100% must have.
Yaroslav Potapov commented
This is really need.
Very important thing. Its inconvenient to write those insert-update loops all time.
Ilya Skorik commented
The most tasty thing for me. Which you promise to make many years