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 1oYxz4-0003KT-2D for pgsql-sql@arkaria.postgresql.org; Thu, 15 Sep 2022 23:09:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oYxz2-00041y-8x for pgsql-sql@arkaria.postgresql.org; Thu, 15 Sep 2022 23:09:24 +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 ) id 1oYxz1-00041p-U2 for pgsql-sql@lists.postgresql.org; Thu, 15 Sep 2022 23:09:24 +0000 Received: from premium22-4.web-hosting.com ([68.65.122.106]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oYxyw-0006P8-J3 for pgsql-sql@lists.postgresql.org; Thu, 15 Sep 2022 23:09:21 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=misuse.org; s=default; h=Content-Type:Cc:To:Subject:Message-ID:Date:From:In-Reply-To: References:MIME-Version:Sender:Reply-To:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=Fp3BFSaxPYuNWrqEeM4g1hsvET5lqjb+wvDMknXS/s8=; b=Gdat2v7P+U+4oaIl+9A5Oui8LB b3kDKnbW9nKyf77r5edAH1i/ajTokS06nWG9zgJKzIIDcOVPTcSefe86zcgABxDurey+nJxBBlVDw 78At7ulnGNdB+uYvE6l2WJxZusAGNthNDz3w0vpwXsuBOFdUBgj0NX3sCYVq9SFm+Fygkg84YNxfX vrhnLxEvZ7741/XY0oUHB3Mhv34LHWdn50nUezgS730SxeDOHd8vEw1ztXzjFT2psBtymowD1C3x1 VsI5mlGGI2nlFqIOT57HXgAHjHCLOym7uyjbMq+gyRETU9s4WTNyXMMGJugkqXyykgUUJLV/qJRBt pGfHtIJg==; Received: from mail-yb1-f171.google.com ([209.85.219.171]:46936) by premium22.web-hosting.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.95) (envelope-from ) id 1oYxyq-00Dk5C-CZ for pgsql-sql@lists.postgresql.org; Thu, 15 Sep 2022 19:09:17 -0400 Received: by mail-yb1-f171.google.com with SMTP id e81so10604550ybb.13 for ; Thu, 15 Sep 2022 16:09:11 -0700 (PDT) X-Gm-Message-State: ACrzQf1QQrs1pE2oG7McMyq6mCZFOWgpYYBkHKc17QItwbCFudCyCaw+ 7DnprdOVMx3WS82Ez5f4QZnBAG2u3KgoRFgw29Q= X-Google-Smtp-Source: AMsMyM6tKO9xeZTJZK1sC36N5ox3b5FLKkoOMWoOgtM+CaeExwnf2v5IWH8UWUYoPuJV+fTWS6ERKstFlvpGnKcTBn0= X-Received: by 2002:a25:7784:0:b0:6ab:4853:e6f9 with SMTP id s126-20020a257784000000b006ab4853e6f9mr2063840ybc.476.1663283351335; Thu, 15 Sep 2022 16:09:11 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Steve Midgley Date: Fri, 16 Sep 2022 00:09:01 +0100 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 To: Inzamam Shafiq Cc: "pgsql-sql@lists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000051376205e8bf55a5" X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - premium22.web-hosting.com X-AntiAbuse: Original Domain - lists.postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - misuse.org X-Get-Message-Sender-Via: premium22.web-hosting.com: authenticated_id: science@misuse.org X-Authenticated-Sender: premium22.web-hosting.com: science@misuse.org X-Source: X-Source-Args: X-Source-Dir: X-From-Rewrite: unmodified, already matched List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000051376205e8bf55a5 Content-Type: text/plain; charset="UTF-8" On Thu, Sep 15, 2022 at 6:32 PM Inzamam Shafiq wrote: > > Hi Team, > > I have a partitioned table in postgresql12 and the unique constraint is > applied on the child tables, when I use ON CONFLICT clause it returned an > error "duplicate key value violates unique constraint "..."", I tried to > replicate the scenario on a test table and created a unique index on > partition column and the unique column but when I try to insert data it > again return the 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? > Please send DDL, sample data (ideally as inserts), and the sql statement that causes the error. Given your description of the problem, I think if you do that, you'll find people on this list solve the issue very quickly for you. Without that, it's hard to diagnose, at least for me. Best, Steve --00000000000051376205e8bf55a5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Thu, Sep 15, 2022 at 6:32 PM Inzam= am Shafiq <inzamam.shafiq@= hotmail.com> wrote:

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 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.=C2=A0

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?
=

Please send DDL, sample data (ideall= y as inserts), and the sql statement that causes the error. Given your desc= ription of the problem, I think if you do that, you'll find people on t= his list solve the issue very quickly for you. Without that, it's hard = to diagnose, at least for me.

Best,
Stev= e
--00000000000051376205e8bf55a5--