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

From: Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com>
To: Pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Date: 2021-07-08 21:01:45
Message-ID: 1879043622.2719673.1625778105678@mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=36b3eb0d51f8bff4b5d445a77d688d88

Thanks,Rj

Browse pgsql-performance by date

  From Date Subject
Next Message Manuel Weitzman 2021-07-08 21:13:07 Re: Strange execution plan
Previous Message Allan Barrielle 2021-07-08 14:12:07 Re: ETL - sql orchestrator is stuck when there is not sleep() between queries