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 (
        content = 'bar'
    RETURNING content
    foo(content) -- values:
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

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.