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

Поиск
Список
Период
Сортировка
От Charles Leifer
Тема Trouble referencing a multi-column unique constraint by name in ONCONFLICT clause
Дата
Msg-id CAPukbqwFpGd+Yh_HSXm4mefHNpZ6NfhXk-iLqVvfL3kShPdJuw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Trouble referencing a multi-column unique constraint by name in ON CONFLICT clause
Список pgsql-general
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 not
insert 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Stephen Frost
Дата:
Сообщение: Re: PG security alerts
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Trouble referencing a multi-column unique constraint by name in ON CONFLICT clause