Insert, on duplicate update in PostgreSQL?

by

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.

More Related Contents:

  1. How to UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?
  2. Postgres: INSERT if does not exist already
  3. Use multiple conflict_target in ON CONFLICT clause
  4. serial in postgres is being increased even though I added on conflict do nothing
  5. How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?
  6. Differentiate inserted and updated rows in UPSERT using system columns
  7. PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values
  8. In psql, why do some commands have no effect?
  9. How to log PostgreSQL queries?
  10. How do you create a read-only user in PostgreSQL?
  11. Generating a UUID in Postgres for Insert statement?
  12. Why does PostgreSQL perform sequential scan on indexed column?
  13. PostgreSQL naming conventions
  14. How to set value of composite variable field using dynamic SQL
  15. In-order sequence generation
  16. Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
  17. Postgres password authentication fails
  18. Generate series of dates – using date type as input
  19. Copy a few of the columns of a csv file into a table
  20. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  21. ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user
  22. How do you create a random string that’s suitable for a session ID in PostgreSQL?
  23. Joining Results from Two Separate Databases
  24. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  25. Update timestamp when row is updated in PostgreSQL
  26. Multicolumn index on 3 fields with heterogenous data types
  27. Select from a table variable
  28. now() default values are all showing same timestamp
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment