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开发者_JS百科 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.

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

Notice the ON CONFLICT part in command Synopsis