postgresql way to insert row with "ON CONFLICT" clause semantics
Is there an easy way in postgres to do the equivalent of the following in sqlite?
INSERT INTO foo (x, y, z) VALUES (1, 2, 3) ON CONFLICT replace;
I've looked around and the solutions I've found are complicated custom functions. The other solution to my problem is to just do
delete from foo where x=1; INSERT INTO foo (x, y, z) VALUES (1, 2, 3) ON CONFLICT replace;
which isn't semantically equivalent but works for my case.
I'd just prefer the ON CONFLICT rule if it doesn't require a custom function.
As of PostgreSQL version 9.1 (beta at this moment), you can use a common table expression to do an insert-or-replace:
/** CREATE TABLE foo(id serial primary key, content text unique); **/ WITH replace AS ( DELETE FROM foo WHERE content = 'bar' RETURNING content ) INSERT INTO foo(content) -- values: SELECT * FROM replace RIGHT JOIN (SELECT CAST('bar' AS text) as content) sub USING(content);
'bar' is the value that will be inserted or replaced.
It's not working in older versions, you have to wait :-(
As of version 9.5, PostgreSQL provides "UPSERT" functionality.
Notice the ON CONFLICT part in command Synopsis