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.

Answers


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.

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

Notice the ON CONFLICT part in command Synopsis


Need Your Help

Fancybox inline flash issue with Firefox

flash fancybox

I have a number of fancyboxes on a page (one in each repeateritem) which show inline flash when a button in the appropriate repeater is pressed.

Configure SVN Server and Trac on Ubuntu

ubuntu svn trac tortoisesvn

I would like to configure my Ubuntu Machine as local web server for multiple project handling.