Geek asks and answers

Postgresql way to insert a row with the semantics of the sentence "ON CONFLICT"

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 looked around, and the solutions I found are complex user-defined functions. Another solution to my problem is just to make

delete from foo where x=1; INSERT INTO foo (x, y, z) VALUES (1, 2, 3) ON CONFLICT replace;

which is not semantically equivalent, but works for my case.

I would prefer a rule ON CONFLICTif it does not require a special function.

+3
source share
2 answers

As with PostgreSQL version 9.1 (currently beta), you can use a common table expression to 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 to be inserted or replaced.

, : - (

+3

9.5, PostgreSQL "UPSERT".

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

ON CONFLICT

+2

More articles:


All Articles