From: Nagaraj Raj <nagaraj.sf@yahoo.com>
To: Pgsql-performance <pgsql-performance@postgresql.org>
Subject: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Date: Thu, 8 Jul 2021 21:01:45 +0000 (UTC)
Message-ID: <1879043622.2719673.1625778105678@mail.yahoo.com> (raw)
References: <1879043622.2719673.1625778105678.ref@mail.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nagaraj.sf@yahoo.com
Subject: Re: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
In-Reply-To: <1879043622.2719673.1625778105678@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox