Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m1b9i-0000Ps-F5 for pgsql-performance@arkaria.postgresql.org; Thu, 08 Jul 2021 21:01:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m1b9h-0007WP-F8 for pgsql-performance@arkaria.postgresql.org; Thu, 08 Jul 2021 21:01:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m1b9h-0007T8-44 for pgsql-performance@lists.postgresql.org; Thu, 08 Jul 2021 21:01:57 +0000 Received: from sonic311-24.consmr.mail.ne1.yahoo.com ([66.163.188.205]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1m1b9d-0001xk-3H for pgsql-performance@postgresql.org; Thu, 08 Jul 2021 21:01:56 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1625778110; bh=ckWetAEEXI/BBQQur7SWSHEJjiTXW5DAFEvrXYM5wMY=; h=Date:From:To:Subject:References:From:Subject:Reply-To; b=DCHgjjlKX4Rhb+lobBpLbz7OcVMZbNw5mG3SjYb158MQfVXE2FEsT05mabTGOnu95FpW1BCSY+XtND9LomydxEn26T3QF7nioFM3i3K/MJC8YikHhHtcv/GgbdFswIpoxJgVg2KjwdhO05r6faqttVKxPXXIT+2QXZFJAMj21kOrKQ16xR7Y2MiYDJvRJ/aZjTVd047HRWnhE3YPf1OCDBMvLnhfpe2yXw+j0B5eMPCxnEkys1kawIRh54m9gh8Dh2HiZYGKB775EZJamt29UEgp1rARSVTafZOHTG6EfLrplz9wG5f51gZoDYgmea1F0ECbBj7c/LB9pja4jUKl0w== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1625778110; bh=kr+bjSU7UduFKpazZvDwx/LogVADdYuIdve3bibgiMW=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=HnurdkQ9Opr4YPk9TRVN4XZ8tXjmKxccwGOM2ansgGhk+KHppugJ2bngN+tRk95/iv/7D0XTSUy6ijiji8BkMYHJ32ti1VTb9mI10KVulTEBGrSg7DyqESQ8U2hp0uVOmKNeFHGdGpM8+IbZbvQn2XONZ1BEYsLtMLx2jxSoagVB6pHJ0wROGFVFEXncjbTm/pPYmjy6ZQ7aRQwa4dUtjYmz0ApcDjevGw2cdADe5joCv7Wchc/7XH3kx3EczK0hFamaPumWIxUdMpJlIrW7cCjLToLI06K72z+mwLsCAjztclgrmQ0vQdvvPGbFCCR00o3AMOceAjEIdGCjUl5BTQ== X-YMail-OSG: 5LBs4BgVM1lfyP_IS8YcVcp_KV8ZxDWgaBMj3cdK.m5uP77Bo5U20LPby2pgVsV .oBVFujiy.5AJNu8po6JLBfLBpfopOzOelzrnlcakiCpHShtMtkr3209Bt494gS.XkUBCCKupuTc 8TJdIpLVEz1pftwUhc_fGAhkdDVTbO4CD3ENXF9UYtTdIuDvAFVADZeu5lrFD1oKu3xQjyJrBEEp LT_wzaEB8RB0sH6NeP2LJbtVEvk5zdo8cAL11apaKyVv3s9k1EI2zCBWy4I1a62jdDiNYfPUXumE 9H08JH9rWIzS12DJV.gD0HV0PfCGO5tr6ui2e.FGD6HFflOJz6r.otrsMUKtgj6nl653nAyjyzE0 k.G6MUm.PykPBu44BPdh6CQ09FvfkfitnOHUajl.S0sLYWCEyjj6oeL8P7wy3xMKptPM5U2pylE0 3R1bLlYaO8r5QNQPBc5ggA1nJmt2n.cXUPDOZidBwL.B3fLw5PJlrHofTbHlFgLYejhFcQKRO3pI DMHh8IKgN7vl4kaZtsPVA0Ku_j5PI1Up7s74bZvnR6qkMmXmZtTHSxexZwEy9R6BylksuCbjugOa vhJEhgPSpUX81Hs602EXZeNa1gNs_RsmCq8PsB_3r.U7zgSo9HME7sKAlz_ePRsURXWW262F.Y8s F7aJm0rdPXZGp68SuNSiKt.CQG12FmsmyhJBsVv3aJXXXQNXFYfnicHOG5tvpwaS4rnXk0O8VHbu JZDn58Noyl70TfxPsYcVTffgAnwrHKVvVH77MZJLXegAdFRIju.FQgf16ndac76j7aSqtgpHxNb0 Fr_2IzBe5lK402g64akYOcGZ9C66UXhKFLjJlQTKjQk80YCTzJGPPKUWdOXXa6VC4XuZftS2tfq8 rF1cfFS3WJhv66kgYGKC0FfXAsI4vvouepoYNdODHuF_6QF66V3AzPhv.4jboVBPyslOmvx6dhgm q_lkuENzxuM4ftdH90PzNQw8NMEBOd.m0cJzNL3ji9rER6K5TH3d6mE53p2RUpPo0QF1HhKVxxZw 3ORAJdDcK49AG8fkxOeI0NM3BDeIcFmiwBHKEdGIXfumE28lFEDKOfPr_1cQs0Kr5lGSbju8kN3w QZ3w0FT4ZSlPiyqFJA21J4aMPyGH1oP.9DZEj9TxuZcxZT266uypK6qW04gcqWPv22kU79DdH2cy lssFFbNhrGSsr6eGkr6mcbSUVCGZO7zr58HbGW83f7CJ5cYKNLsrEEweeSWPQi4BoS0ke2UBdCND FKcl.MLo.6AIj57YN_I.kfy5cvHr4bwJKGryUCF6O0nv4iACBbJXeVs9tTGNaAesrRTdjsuBTr8K OFfJGm5iIHixUDKfnFKXkXjTPXW8uRyQoO8T.iPI.ik1M8cXDOhmGVIORd.tk6.Igvk3OkccK0ye bktxHeozGwJ_bMZTTR.tvttZCcva7.Cu2raBgbeC.F8lbORsu6jwLL7fhkHUOme1DLjXx0P3Skc7 2UVvL.ACzP19pn_yen2DPqu1yhRqm8dmxsYB28Noo_Jsr7E4iuKlbMLWcgSbyQnNUJciaU4um1OM c9xJ2kCFWEzvkmmiZUXLW7MR1SBbmATMmF__4e7vbeE_iMiPcxnFF1AvrUu623hfBRnMmv50pEld 4pzYVD_Tqg7NSJJOouGx6qITcXMw.ZZrV1Rl7BVSop09YOmA8xvu0vPImIIJRBVq7Xp8czJF67QD 0eWtCutBxyZ7rlb1nzP3dcxju1a45aVNyNeJ410kIVpZPAi5cXKUlD33RvtLyF2oh3t.GXugZ5xY F5vnEcy3T8BbMlaKcOs.xTj1dRUgwA_CsANFRG7kgVG6B1UI1lT8Y29hUY9NO.wglwCx_jFPIJJ3 Pm_vL46uDLq.OB0Ww6OZqEEoV9NqIm6OR3juCotD_xEAmbSa9XU5UhTG99LvNU22nfMfyDwVM5gZ TNOKwuUyofK3.jZOTohVJITAF9k0IFeRfMFc2s0EDFxUtBTcajxZ648449tvbZDUjjRH3Iv52yzU QJA7PXba91iSUsY8sQqgPAgHer7gRZXe17qW9ZEQGBS.lcrldhQ1KO_GAygJTe7puN.Xjp8rTFVx iNUYcQBDgAaAd43PgIKLHU4.DkHaZuNvV5__N8JD3JuNx_BPBHK1j9HJ6oc3uhwEurZuAwYQzLjx y6Fee0kTZ4PUGG2fKD26_TWzZxB4o1l_VLjb2_cllmcanMiGDspeAVSsJiOw1.6ZwXOYw_NO_iTv JAovRIRamZe8p_gKBa_OpPnvYCbcmn2elESsjSDMxP2q7yLLyH4PLjcJCr8JRz529mrbBmfW0l_2 rVPh4B6_wTfKmz.NGMbwTDWXY2Hwq0Uuiu1zTVvWkgyON6N8gYACZAsX9HPBA3n9eBL4ICWMcXY8 k.4gBgc.FRFN3rrXqy_ISG3Z6h7QjjMXqpmt2jtZi9JQLl5Y7Mx826Hnej4MDJzaN3JrllGadzbZ oXX.5edPgdLmpxq209DWeGekgy96KNf7gVAK0_s.5t_tg X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.ne1.yahoo.com with HTTP; Thu, 8 Jul 2021 21:01:50 +0000 Date: Thu, 8 Jul 2021 21:01:45 +0000 (UTC) From: Nagaraj Raj To: Pgsql-performance Message-ID: <1879043622.2719673.1625778105678@mail.yahoo.com> Subject: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2719672_1388384591.1625778105677" References: <1879043622.2719673.1625778105678.ref@mail.yahoo.com> X-Mailer: WebService/1.1.18469 YMailNorrin Content-Length: 4687 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_2719672_1388384591.1625778105677 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello everyone, I have a scenario where wanted to add PK on partition to make sure to monit= or unique values for two columns values. but as PG required to partition co= lumn 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(d= t);--create unique index on t(id,pid);--alter table t=C2=A0 add constraint = uk unique=C2=A0 (id);--create unique index on t(id,pid);alter table t=C2=A0= add constraint uk unique=C2=A0 (id,pid,dt); create table t1 partition of t for values from ('2020-01-01') to ('2020-02-= 01');alter table t1=C2=A0 add constraint uk1 unique=C2=A0 (id,pid);create t= able t2 partition of t for values from ('2020-02-01') to ('2020-03-01');alt= er table t2=C2=A0 add constraint uk2 unique=C2=A0 (id,pid);create table t4 = partition of t for values from ('2020-03-01') to ('2020-04-01');alter table= t4=C2=A0 add constraint uk3 unique=C2=A0 (id,pid);create table t3 partitio= n of t for values from ('2020-04-01') to ('2020-05-01');alter table t3=C2= =A0 add constraint uk4 unique=C2=A0 (id,pid); insert into t(id,pid,name,dt) values (1,2,'raj','2020-01-01')on conflict (i= d,pid) do nothing; ERROR: there is no unique or exclusion constraint matching the ON CONFLICT = specification https://dbfiddle.uk/?rdbms=3Dpostgres_11&fiddle=3D36b3eb0d51f8bff4b5d445a77= d688d88 Thanks,Rj ------=_Part_2719672_1388384591.1625778105677 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hello everyone,

I have a s= cenario 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 shoul= d 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 inde= x on t(id,pid);
--alter table t  add constraint uk unique&nb= sp; (id);
--create unique index on t(id,pid);
alter table t  add constraint uk unique  (id,pid,dt);

create table t= 1 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,pi= d);
create table t4 partition of t for values from ('2020-03-01')= to ('2020-04-01');
alter table t4  add constraint uk3 uniqu= e  (id,pid);
create table t3 partition of t for values from = ('2020-04-01') to ('2020-05-01');
alter table t3  add constr= aint uk4 unique  (id,pid);

insert = into t(id,pid,name,dt) values (1,2,'raj','2020-01-01')
on conflic= t (id,pid) do nothing;

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



Thanks,
Rj ------=_Part_2719672_1388384591.1625778105677--