Postgres: on conflict with implicit conflict target

3
votes

Postgres 10 and 11 spec for insert says:

ON CONFLICT [ conflict_target ] conflict_action

I have a table:

create table c (
    e text not null,
    m text not null,
    v numeric not null,
    PRIMARY KEY (e, m)
)

and I want to do

insert into candle values (...) 
    on conflict do update set
            v = 5

but I get an error:

ON CONFLICT DO UPDATE requires inference specification or constraint name Hint: For example, ON CONFLICT (column_name)

Why do I have to provide a conficting target? How to provide primary key or some other set of columns?

2
sqlpostgresqlupsert

2 Answers

8
votes

Yes, the conflict_target is optional, but only with DO NOTHING.

Per the documentation:

For ON CONFLICT DO NOTHING, it is optional to specify a conflict_target; when omitted, conflicts with all usable constraints (and unique indexes) are handled. For ON CONFLICT DO UPDATE, a conflict_target must be provided.

3
votes

You could have multiple constraints and therefore multiple alternative actions.

You can simply specify the column names:

insert into candle values (...) 
on conflict (e,m) do update set
  v = 5