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
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=36b3eb0d51f8bff4b5d445a77d688d88
Thanks,Rj
