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

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