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 <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1oYsiS-0000Os-FC
	for pgsql-sql@arkaria.postgresql.org; Thu, 15 Sep 2022 17:31:57 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1oYsiR-0005IZ-Bn
	for pgsql-sql@arkaria.postgresql.org; Thu, 15 Sep 2022 17:31:55 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <inzamam.shafiq@hotmail.com>)
	id 1oYsiQ-0005IQ-Cy
	for pgsql-sql@lists.postgresql.org; Thu, 15 Sep 2022 17:31:55 +0000
Received: from mail-oln040092067067.outbound.protection.outlook.com
 ([40.92.67.67] helo=EUR02-AM5-obe.outbound.protection.outlook.com)
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <inzamam.shafiq@hotmail.com>)
	id 1oYsiN-0003lq-DP
	for pgsql-sql@lists.postgresql.org; Thu, 15 Sep 2022 17:31:53 +0000
ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none;
 b=KDEYvXfKkGeZ/85x3jo+KupAHe2kzL1sAyd7UxBH7plvzaPBrgPERHS5n7P/1o3Ual4PZJ4ctJ/dMz/LSwrvQULb16F0gjZCfGzk7e59KgKQCMiJlOTXa/z5/UrR2FCT/38YqzURe7Kw5RGvBIw+2UxhQoospSvEbWKzuaLD+h+G4iitT2q+h2v/ldFZ63BULH5Mg2sFlq0kvwqqdhl8Dg1bMAt7yuLtkADxU+vTAIvKrSvAM90nPr6kbLSBsBAjq0Pt1BZRPhH/DCRS+c2jPyoitFKob21kKcsOJ7dLgkkNLHfFx/45/qd2G56BB1/xGC8ceH+4MgBmePP1vJok8w==
ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com;
 s=arcselector9901;
 h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1;
 bh=R1dZAEly4Fy8+yDEZxqzxCvadnOCbPGnibpjGxywxyY=;
 b=ZzU4x4hUsrbxCTmrOP1U2Buc+/7VUBZO9xoE/FRyZ0uj9t1txITu0jlNzuwqUHII0uvgB8IUCYZ1IHwyC+vN/vgIFC4+HRBoomurGTcrKnjDGDVJwsSAFxx8fbrz8VGvCRumSkqO/pDZw9cLJnMlxPhs0BhVVeMAJdZLOcnKnpGCYKfAx2ZvNE7PVFMFw6nVHLlVnmhDrbrb92ghjgzleSngEc/c8SBDDhrTElkinudELXRutjvByC4r8J9ZQBziQhMmME4Yjc+B11BDHSYcd29ccbbgLUOWSKZO3oe2aT3IeFXVV4uPSwAkw0m6Da8g5PJ01JEp9OCopNyIDmkQ0Q==
ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none;
 dkim=none; arc=none
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hotmail.com;
 s=selector1;
 h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck;
 bh=R1dZAEly4Fy8+yDEZxqzxCvadnOCbPGnibpjGxywxyY=;
 b=HMfcVj3FPNaVhhS3ygzDlz1selcXmIS2uxaQjxIrElIzyUv/tvXmSB/yhdKXEAqJkBaG7AsXVEDuRdJZgWtP7xP1z7i34ITDRdoSJYNmekBI8TmFaHwfnXGiOjQ6k07YZqsHMLGSe3010tf2G8O70zKHsPKAm+c13TzxC9xGCxbd3KQ5M6LO06CwewkbDL4PvDFIcX+MSb7GAy/vZVA9I2ecpBBnoWVgrBFN9Pz1Vaq6O48kOH96f0dl28cPdDpj9DeRjLqckFeW5ca8Ivv5fz8kCkgnNbval0M3sMTztBAIAVzlHWnxS1lkVNWw3hYubkVQAIIgOnOIElMf3NlA1w==
Received: from AM9P251MB0330.EURP251.PROD.OUTLOOK.COM (2603:10a6:20b:41c::6)
 by GV1P251MB0793.EURP251.PROD.OUTLOOK.COM (2603:10a6:150:50::14) with
 Microsoft SMTP Server (version=TLS1_2,
 cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.5612.22; Thu, 15 Sep
 2022 17:31:47 +0000
Received: from AM9P251MB0330.EURP251.PROD.OUTLOOK.COM
 ([fe80::2406:c3cd:5409:e003]) by AM9P251MB0330.EURP251.PROD.OUTLOOK.COM
 ([fe80::2406:c3cd:5409:e003%9]) with mapi id 15.20.5632.015; Thu, 15 Sep 2022
 17:31:47 +0000
From: Inzamam Shafiq <inzamam.shafiq@hotmail.com>
To: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
Thread-Topic: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
Thread-Index: AQHYySkBTuMxSY/kAUSUBrMh2UAU7w==
Date: Thu, 15 Sep 2022 17:31:47 +0000
Message-ID: 
 <AM9P251MB033090199F1A3C2B1742A50A98499@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
msip_labels: 
x-tmn: [5MMncylDQj6aiYjjMejWSkrArx0R7ZSnR26Bxu7zlTvyfeYelY6gKSomDWLFo5rC]
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: AM9P251MB0330:EE_|GV1P251MB0793:EE_
x-ms-office365-filtering-correlation-id: 2181071f-9de6-4226-a1a6-08da97402a79
x-microsoft-antispam: BCL:0;
x-microsoft-antispam-message-info: 
 ZwOFKxLXQD22eB+gTuy0TUYicJlKbJHscLBAr5scd7XW3jcubQ7S9EWMDXiHm+bARpZ+v2pY8pwPqSx525m1OMjZeApDfib4cyrIVWoCMTLxxVy10xxETPJU4AirCJS0sYqWD1M0+2Ha9vdaKvYL3Sb+4sSEudnN+1+uuUZOwqzTmpswkxT3PWPEUDJawVkcyCkyFeqpnJ6ahaoMbr0MRBubKDZM1+fF4dIRM5TAoo9ThoUOHUq0x+kkDbttRuxBhvkxYSaDNt+IbXt140p+QepJsnHyLudxijod8JjEJcpCC/2aBRoidLz1TN326Jq/hspOsrIkD63rvyJet/p1BKeasvyE1HvdYjpQoVh6PvF+paL9ccpkxDZs3YykuCSdFBJaNqRri8dU1LuGUhRM6WpvCRla0f2RZQtp528oE7We2PWCeQAe5mVwsh5JbOmLxqEcSY5l3jPDagplrxXmvcGwSPYy/BrA+kLDPQrOV0aIj0GUu/A7ZaACF/vKPBgx1/LAZi++FYHY+bm9rBosHIPu/yBTCcoqc4+lHBByVaAyWn4E6YQGeyz/58UEirxUuAe3jUWOOmIzsSqYDU0MTJHgI6c3u2GMr2nJ3NkC+gCKnWoCJUdKnbw2qx6OO5DDyxlGpFK/OoDxlNzRKPwx3Q==
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?iso-8859-1?Q?Iy4PMdhx1HQndh3iIAC5FCiRHJTSlNrBIjaT2kt63PLxE+XI6s5HY7XJwj?=
 =?iso-8859-1?Q?h73x4mGbo19klPsdWnTNGEcSv4NEybwWWs+1B9UMny8sjJ8hcd2ju8sAQO?=
 =?iso-8859-1?Q?tSyTxo7JRd8XXC46B+QZLN1TI+gi5SrQtk9mcLn/CrG8LyKsSHnYo47Bxa?=
 =?iso-8859-1?Q?venHI2Z2u6XlKb4/GMQCEYqV0T9BkIvHxbyrNC+nezw5tt8EhNArTXQYtj?=
 =?iso-8859-1?Q?dNA5tYWg1DjSCNN4UKsyPn9CDSXRzLjZv1dlaeqb7Bl1Q8VRJFbpemeSiq?=
 =?iso-8859-1?Q?negUfpNd2woxFxgLPS7lkq6CmE2mDc0ows+/nfnM77adXd1nCrGB0Nrw33?=
 =?iso-8859-1?Q?P3q1GB6YqDmHPK8jssjE0I84PZ7BcKcZG4ZAQ5abdYaGxZTHguyFQF7EUN?=
 =?iso-8859-1?Q?GKSxnmam7V2j/ZpQcOU5b01N281DsdSDuVkGORIjX7hxFY+onYP4yK6EbM?=
 =?iso-8859-1?Q?lHifG8oivQ1G/7PzYxWH7yPvQpTxxIzWrcoyGgXarWw17EZmk2iWK8BjdL?=
 =?iso-8859-1?Q?gFe5rcCFunSScNf+u08PvlOTUbcLqFKtgfWTK/wa+7AZB9P2xT/MI4DKoi?=
 =?iso-8859-1?Q?R7Jb1YdGo7h3VWTQ1rtFPpS/OtjDlL+s9Hrp68xJlW/r0BtJ5JlTgPnfZ6?=
 =?iso-8859-1?Q?IsozgEWlRY19lzTsvADasEmdX1Ab6BuRRpDFX95oKJh0tdyQ2yJhMzRfME?=
 =?iso-8859-1?Q?IKqumTLlDGgCnhZJA4FKnL2lqfxzlMPDOI+eO+DekRxWOzl6f6UILm/FiR?=
 =?iso-8859-1?Q?TOV9GO4A9zyxDbpI2ouTfqZRsgBYZc1GaYfe/fBRL0LK8BItpif6cgltTK?=
 =?iso-8859-1?Q?OdoKkSzoF4YsoH6IMuhogvuWBHm8Wm2u5DCqx1SK+anDj49yoRz222JAwy?=
 =?iso-8859-1?Q?5J05eFFGDdIzaeJAx/ysBm7I4P2cHhh7hEDGyvlxbTrJurH7e8CaiP4SmR?=
 =?iso-8859-1?Q?BVJu0hsVoycWuMEmoVQd7AjjX9B3r0sMVV4kCK+cO6BZcfnF/k+6H3z86z?=
 =?iso-8859-1?Q?uYOtp26+EWIFLwFEOK0+gRoeFZ2jM0ZpLo8b8soCHhzdrTYyPFOfrby8DE?=
 =?iso-8859-1?Q?27lD2dw3K8zNhrXznHEzWpqW+5u26I4C6p9Rlb7Ql20tzW+/CxK5RO5xDY?=
 =?iso-8859-1?Q?/skHhBFJuebfVNtx8X5qT3QleAQHo4icZEcqHa+oISkEU6Bvb1MxJnEbjA?=
 =?iso-8859-1?Q?6VEYarxowXiZMNEeRiJJbgAdlkYH1AFkSQ9Rqacm2cpNsU5q7mfzqmoWrZ?=
 =?iso-8859-1?Q?Lp9N80+5osNJ7t/cW+FX/joLTYLcUZLWc7e0P+XLkUT2SVL2ix+c8FWe3t?=
 =?iso-8859-1?Q?bfw4Gkvsr3twQABnbDl8Kb5n8Rfx5pr7xWSjXUZq40nvw3TaMHslAC+sSL?=
 =?iso-8859-1?Q?GUN+of5hJ8rQjdKVHLMwzeZaSDZ+YttA=3D=3D?=
Content-Type: multipart/alternative;
	boundary="_000_AM9P251MB033090199F1A3C2B1742A50A98499AM9P251MB0330EURP_"
MIME-Version: 1.0
X-OriginatorOrg: sct-15-20-4755-11-msonline-outlook-00b75.templateTenant
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: AM9P251MB0330.EURP251.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
 00000000-0000-0000-0000-000000000000
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 2181071f-9de6-4226-a1a6-08da97402a79
X-MS-Exchange-CrossTenant-originalarrivaltime: 15 Sep 2022 17:31:47.2037
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 
 00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: GV1P251MB0793
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Archived-At: 
 <https://www.postgresql.org/message-id/AM9P251MB033090199F1A3C2B1742A50A98499%40AM9P251MB0330.EURP251.PROD.OUTLOOK.COM>
Precedence: bulk

--_000_AM9P251MB033090199F1A3C2B1742A50A98499AM9P251MB0330EURP_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hi Team,

I have a partitioned table in postgresql12 and the unique constraint is app=
lied on the child tables, when I use ON CONFLICT clause it returned an erro=
r "duplicate key value violates unique constraint "..."", I tried to replic=
ate the scenario on a test table and created a unique index on partition co=
lumn and the unique column but when I try to insert data it again return th=
e same error.

This works successfully when I directly insert data in the child table.

What could be the way forward to get this work on parent table?

Thanks.

Cheers,

Inzamam Shafiq
Sr. DBA

--_000_AM9P251MB033090199F1A3C2B1742A50A98499AM9P251MB0330EURP_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-=
1">
<style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo=
ttom:0;} </style>
</head>
<body dir=3D"ltr">
<div style=3D"font-family: &quot;Franklin Gothic Book&quot;, &quot;Avenir N=
ext Condensed&quot;, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" cla=
ss=3D"elementToProof">
<br>
</div>
<span class=3D"x_elementToProof" style=3D"font-size:12pt;margin:0px;backgro=
und-color:rgb(255, 255, 255)">Hi Team,</span>
<div class=3D"x_elementToProof" style=3D"font-size:12pt;margin:0px;backgrou=
nd-color:rgb(255, 255, 255)">
<br>
</div>
<div class=3D"x_elementToProof" style=3D"font-size:12pt;margin:0px;backgrou=
nd-color:rgb(255, 255, 255)">
I have a partitioned table in postgresql12 and the unique constraint is app=
lied on the child tables, when I use ON CONFLICT clause it returned an erro=
r &quot;<span style=3D"font-size:13px;font-family:monospace;margin:0px;colo=
r:rgb(169, 68, 66);text-align:left;background-color:rgb(245, 245, 245);disp=
lay:inline !important">duplicate
 key value violates unique constraint &quot;...&quot;</span>&quot;, I tried=
 to replicate the scenario on a test table and created a unique index on pa=
rtition column and the unique column but when I try to insert data it again=
 return the same error.&nbsp;</div>
<div class=3D"x_elementToProof" style=3D"font-size:12pt;margin:0px;backgrou=
nd-color:rgb(255, 255, 255)">
<br>
</div>
<div class=3D"x_elementToProof" style=3D"font-size:12pt;margin:0px;backgrou=
nd-color:rgb(255, 255, 255)">
This works successfully when I directly insert data in the child table.</di=
v>
<div class=3D"x_elementToProof" style=3D"font-size:12pt;margin:0px;backgrou=
nd-color:rgb(255, 255, 255)">
<br>
</div>
<div class=3D"x_elementToProof" style=3D"font-size:12pt;margin:0px;backgrou=
nd-color:rgb(255, 255, 255)">
What could be the way forward to get this work on parent table?</div>
<div class=3D"x_elementToProof" style=3D"font-size:12pt;margin:0px;backgrou=
nd-color:rgb(255, 255, 255)">
<br>
</div>
<div class=3D"x_elementToProof" style=3D"font-size:12pt;margin:0px;backgrou=
nd-color:rgb(255, 255, 255)">
Thanks.</div>
<div style=3D"font-size:15px;font-family:&quot;Segoe UI&quot;, &quot;Segoe =
UI Web (West European)&quot;, &quot;Segoe UI&quot;, -apple-system, BlinkMac=
SystemFont, Roboto, &quot;Helvetica Neue&quot;, sans-serif;margin:0px;color=
:rgb(32, 31, 30);background-color:rgb(255, 255, 255)">
<div style=3D"font-size:12pt;font-family:&quot;Franklin Gothic Book&quot;, =
&quot;Avenir Next Condensed&quot;, sans-serif;margin:0px;color:rgb(0, 0, 0)=
">
<br>
</div>
<div style=3D"margin:0px">
<div style=3D"margin:0px"><span dir=3D"ltr" style=3D"font-size:12pt;font-fa=
mily:Calibri, Helvetica, sans-serif;margin:0px;color:rgb(0, 0, 0)">Cheers,<=
/span></div>
</div>
</div>
<div>
<div id=3D"Signature">
<div>
<div id=3D"divtagdefaultwrapper" dir=3D"ltr" style=3D"font-size:12pt; color=
:#000000; font-family:Calibri,Helvetica,sans-serif">
<div><br>
</div>
<div><i>Inzamam Shafiq</i></div>
<div><i>Sr. DBA</i></div>
<div><i></i></div>
</div>
</div>
</div>
</div>
</body>
</html>

--_000_AM9P251MB033090199F1A3C2B1742A50A98499AM9P251MB0330EURP_--