Trouble referencing a multi-column unique constraint by name in ON CONFLICT clause

Started by Charles Leiferover 7 years ago3 messagesgeneral
Jump to latest
#1Charles Leifer
coleifer@gmail.com

Hi,

I'm running into behavior I don't understand when trying to do an UPSERT
with Postgres. The docs would seem to indicate that the conflict target of
the INSERT statement can be either an index expression or a constraint
name. However, when attempting to reference the constraint name, I get a
"column ... does not exist" error.

My first attempt was to just create a UNIQUE index, which works fine with
the constraint inference:

create table kv (key text, value text, extra text);create unique index
kv_key_value on kv(key, value);insert into kv (key, value) values
('k1', 'v1');-- this works:insert into kv (key, value, extra) values
('k1', 'v1', 'e1')
on conflict (key, value) do update set extra=excluded.extra;
-- this does notinsert into kv (key, value, extra) values ('k1', 'v1', 'e1')
on conflict (kv_key_value) do update set extra=excluded.extra;

Describing the above table, I see the following under "Indexes:"

"kv_key_value" UNIQUE, btree (key, value)

My second try was to put the unique constraint explicitly in the create
table:

create table kv (
key text,
value text,
extra text,
constraint kv_key_value unique(key, value));

Describing the above table, the output of "Indexes:" is slightly different
("UNIQUE CONSTRAINT" vs "UNIQUE" in previous example):

"kv_key_value" UNIQUE CONSTRAINT, btree (key, value)

However I am still unable to specify the constraint name as the conflict
target:

insert into kv (key, value, extra) values ('k1', 'v1', 'e1')
on conflict (kv_key_value) do update set extra=excluded.extra;
ERROR: column "kv_key_value" does not exist
LINE 2: on conflict (kv_key_value) do update set extra=exclude...

Am I misunderstanding something here? I totally get that I can use the
equivalent expression and rely on constraint inference, but I'd like to
know why the constraint name doesn't appear to work when the docs make it
sound like it should?

Thanks so much for your help,

Charlie

PS - StackOverflow question of the above, if anyone wants to answer there:
https://stackoverflow.com/questions/52542845/postgresql-on-conflict-with-multi-column-unique-constraint-name

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Charles Leifer (#1)
Re: Trouble referencing a multi-column unique constraint by name in ON CONFLICT clause

Charles Leifer <coleifer@gmail.com> writes:

I'm running into behavior I don't understand when trying to do an UPSERT
with Postgres. The docs would seem to indicate that the conflict target of
the INSERT statement can be either an index expression or a constraint
name. However, when attempting to reference the constraint name, I get a
"column ... does not exist" error.

What I see in the INSERT reference page is

where conflict_target can be one of:

( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name

So you can write a parenthesized list of column names, or you can write
"ON CONSTRAINT constraint_name". Given your second example with

create table kv (
key text,
value text,
extra text,
constraint kv_key_value unique(key, value));

either of these work for me:

regression=# insert into kv (key, value, extra) values ('k1', 'v1', 'e1')
on conflict (key, value) do update set extra=excluded.extra;
INSERT 0 1
regression=# insert into kv (key, value, extra) values ('k1', 'v1', 'e1')
on conflict on constraint kv_key_value do update set extra=excluded.extra;
INSERT 0 1

regards, tom lane

#3Charles Leifer
coleifer@gmail.com
In reply to: Tom Lane (#2)
Re: Trouble referencing a multi-column unique constraint by name in ON CONFLICT clause

Many thanks, sorry for missing something so obvious!

On Thu, Sep 27, 2018 at 1:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Show quoted text

Charles Leifer <coleifer@gmail.com> writes:

I'm running into behavior I don't understand when trying to do an UPSERT
with Postgres. The docs would seem to indicate that the conflict target

of

the INSERT statement can be either an index expression or a constraint
name. However, when attempting to reference the constraint name, I get a
"column ... does not exist" error.

What I see in the INSERT reference page is

where conflict_target can be one of:

( { index_column_name | ( index_expression ) } [ COLLATE collation ] [
opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name

So you can write a parenthesized list of column names, or you can write
"ON CONSTRAINT constraint_name". Given your second example with

create table kv (
key text,
value text,
extra text,
constraint kv_key_value unique(key, value));

either of these work for me:

regression=# insert into kv (key, value, extra) values ('k1', 'v1', 'e1')
on conflict (key, value) do update set extra=excluded.extra;
INSERT 0 1
regression=# insert into kv (key, value, extra) values ('k1', 'v1', 'e1')
on conflict on constraint kv_key_value do update set
extra=excluded.extra;
INSERT 0 1

regards, tom lane