ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Поиск
Список
Период
Сортировка
От Nagaraj Raj
Тема ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Дата
Msg-id 1879043622.2719673.1625778105678@mail.yahoo.com
обсуждение исходный текст
Список pgsql-performance
Дерево обсуждения
Hello everyone,

I have a scenario where wanted to add PK on partition to make sure to monitor unique values for two columns values. but as PG required to partition column should be part of PK. How can we make sure actual two columns need to be unique values.

and also while insert into table need be use 'on conflict'.

create table t (id int, pid int , name name , dt date) partition by range(dt);
--create unique index on t(id,pid);
--alter table t  add constraint uk unique  (id);
--create unique index on t(id,pid);
alter table t  add constraint uk unique  (id,pid,dt);

create table t1 partition of t for values from ('2020-01-01') to ('2020-02-01');
alter table t1  add constraint uk1 unique  (id,pid);
create table t2 partition of t for values from ('2020-02-01') to ('2020-03-01');
alter table t2  add constraint uk2 unique  (id,pid);
create table t4 partition of t for values from ('2020-03-01') to ('2020-04-01');
alter table t4  add constraint uk3 unique  (id,pid);
create table t3 partition of t for values from ('2020-04-01') to ('2020-05-01');
alter table t3  add constraint uk4 unique  (id,pid);

insert into t(id,pid,name,dt) values (1,2,'raj','2020-01-01')
on conflict (id,pid) do nothing;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification



Thanks,
Rj

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

Предыдущее
От: Allan Barrielle
Дата:
Сообщение: Re: ETL - sql orchestrator is stuck when there is not sleep() between queries
Следующее
От: Manuel Weitzman
Дата:
Сообщение: Re: Strange execution plan