Replace Update or Insert a row Into PostgreSQL Table

Shiji Pan

11 years ago

In Mysql, if you want to either updates or inserts a row in a table, depending if the table already has a row that matches the data, you can use “ON DUPLICATE KEY UPDATE”.

How to do it in PostgreSQL?

A 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.

With PostgreSQL 9.1 this can be achieved using a writeable CTE:

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, field2
FROM new_values
WHERE NOT EXISTS (SELECT 1
                  FROM upsert up
                  WHERE up.id = new_values.id)

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;

You can see this page for more about PostgreSQL “Upsert”: https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql