postgresql way to insert row with "ON CONFLICT" clause semantics

Question

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.

possible duplicate of [Insert, on duplicate update (postgresql)](http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql) — Frank Farmer, May 03 '11 at 19:21
Long story short, no. There is no simple syntax for this in postgres. — Frank Farmer, May 03 '11 at 19:22
I read that question. His case wants to do an insert or update. I need an insert or replace. — Falmarri, May 03 '11 at 19:22
The answer is the same. There is no simple syntax for this in postgres, and you'll have to use a procedure/multiple queries. — Frank Farmer, May 03 '11 at 19:24

score 4 · Accepted Answer · answered May 04 '11 at 08:46

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 :-(

Note that this is *not* safe in the presence of concurrency unless you take a table-level lock. — Craig Ringer, Sep 29 '14 at 09:41

score 2 · Answer 2 · answered Jan 15 '16 at 16:05

2

As of version 9.5, PostgreSQL provides "UPSERT" functionality.

http://www.postgresql.org/docs/current/static/sql-insert.html

Notice the ON CONFLICT part in command Synopsis

answered Jan 15 '16 at 16:05

Nicolae Babanu

41
2

postgresql way to insert row with "ON CONFLICT" clause semantics

2 Answers2