Insert, on duplicate update in PostgreSQL?

PostgreSQL since version 9.5 has UPSERT syntax, with ON CONFLICT clause. with the following syntax (similar to MySQL)

INSERT INTO the_table (id, column_1, column_2) VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')ON CONFLICT (id) DO UPDATE   SET column_1 = excluded.column_1,       column_2 = excluded.column_2;

Searching postgresql's email group archives for "upsert" leads to finding an example of doing what you possibly want to do, in the manual:

Example 38-2. Exceptions with UPDATE/INSERT

This example uses exception handling to perform either UPDATE or INSERT, as appropriate:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS$$BEGIN    LOOP        -- first try to update the key        -- note that "a" must be unique        UPDATE db SET b = data WHERE a = key;        IF found THEN            RETURN;        END IF;        -- not there, so try to insert the key        -- if someone else inserts the same key concurrently,        -- we could get a unique-key failure        BEGIN            INSERT INTO db(a,b) VALUES (key, data);            RETURN;        EXCEPTION WHEN unique_violation THEN            -- do nothing, and loop to try the UPDATE again        END;    END LOOP;END;$$LANGUAGE plpgsql;SELECT merge_db(1, 'david');SELECT merge_db(1, 'dennis');

There's possibly an example of how to do this in bulk, using CTEs in 9.1 and above, in the hackers mailing list:

WITH foos AS (SELECT (UNNEST(%foo[])).*)updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)WHERE updated.id IS NULL;

See a_horse_with_no_name's answer for a clearer example.

Warning: this is not safe if executed from multiple sessions at the same time (see caveats below).

Another clever way to do an "UPSERT" in postgresql is to do two sequential UPDATE/INSERT statements that are each designed to succeed or have no effect.

UPDATE table SET field='C', field2='Z' WHERE id=3;INSERT INTO table (id, field, field2)       SELECT 3, 'C', 'Z'       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

The UPDATE will succeed if a row with "id=3" already exists, otherwise it has no effect.

The INSERT will succeed only if row with "id=3" does not already exist.

You can combine these two into a single string and run them both with a single SQL statement execute from your application. Running them together in a single transaction is highly recommended.

This works very well when run in isolation or on a locked table, but is subject to race conditions that mean it might still fail with duplicate key error if a row is inserted concurrently, or might terminate with no row inserted when a row is deleted concurrently. A SERIALIZABLE transaction on PostgreSQL 9.1 or higher will handle it reliably at the cost of a very high serialization failure rate, meaning you'll have to retry a lot. See why is upsert so complicated, which discusses this case in more detail.

This approach is also subject to lost updates in read committed isolation unless the application checks the affected row counts and verifies that either the insert or the update affected a row.

With PostgreSQL 9.1 this can be achieved using a writeable CTE (common table expression):

WITH new_values (id, field1, field2) as (  values      (1, 'A', 'X'),     (2, 'B', 'Y'),     (3, 'C', 'Z')),upsert as(     update mytable m         set field1 = nv.field1,            field2 = nv.field2    FROM new_values nv    WHERE m.id = nv.id    RETURNING m.*)INSERT INTO mytable (id, field1, field2)SELECT id, field1, field2FROM new_valuesWHERE NOT EXISTS (SELECT 1                   FROM upsert up                   WHERE up.id = new_values.id)

See these blog entries:

• Upserting via Writeable CTE
• WAITING FOR 9.1 – WRITABLE CTE
• WHY IS UPSERT SO COMPLICATED?

Note that this solution does not prevent a unique key violation but it is not vulnerable to lost updates.
See the follow up by Craig Ringer on dba.stackexchange.com