postgresql - Use multiple conflict_target in ON CONFLICT clause

Question

I have two columns in table col1, col2, they both are unique indexed (col1 is unique and so is col2).

I need at insert into this table, use ON CONFLICT syntax and update other columns, but I can't use both column in conflict_targetclause.

It works:

INSERT INTO table
...
ON CONFLICT ( col1 ) 
DO UPDATE 
SET 
-- update needed columns here

But how to do this for several columns, something like this:

...
ON CONFLICT ( col1, col2 )
DO UPDATE 
SET 
....

Answer 1

ON CONFLICT requires a unique index* to do the conflict detection. So you just need to create a unique index on both columns:

t=# create table t (id integer, a text, b text);
CREATE TABLE
t=# create unique index idx_t_id_a on t (id, a);
CREATE INDEX
t=# insert into t values (1, 'a', 'foo');
INSERT 0 1
t=# insert into t values (1, 'a', 'bar') on conflict (id, a) do update set b = 'bar';
INSERT 0 1
t=# select * from t;
 id | a |  b  
----+---+-----
  1 | a | bar

* In addition to unique indexes, you can also use exclusion constraints. These are a bit more general than unique constraints. Suppose your table had columns for id and valid_time (and valid_time is a tsrange), and you wanted to allow duplicate ids, but not for overlapping time periods. A unique constraint won't help you, but with an exclusion constraint you can say "exclude new records if their id equals an old id and also their valid_time overlaps its valid_time."