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
