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 1oZ52l-00087t-6M for pgsql-sql@arkaria.postgresql.org; Fri, 16 Sep 2022 06:41:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oZ52j-0004Wn-AE for pgsql-sql@arkaria.postgresql.org; Fri, 16 Sep 2022 06:41:41 +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 1oZ52i-0004We-TR for pgsql-sql@lists.postgresql.org; Fri, 16 Sep 2022 06:41:40 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oZ52c-0001Rv-ET for pgsql-sql@lists.postgresql.org; Fri, 16 Sep 2022 06:41:39 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 28G6fTYE3585171; Fri, 16 Sep 2022 02:41:29 -0400 From: Tom Lane To: Inzamam Shafiq cc: Steve Midgley , "pgsql-sql@lists.postgresql.org" Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 In-reply-to: References: Comments: In-reply-to Inzamam Shafiq message dated "Fri, 16 Sep 2022 05:49:43 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <3585169.1663310489.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 16 Sep 2022 02:41:29 -0400 Message-ID: <3585170.1663310489@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Inzamam Shafiq writes: > Following is the DDL, > CREATE TABLE testpart ( > =E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82id bigserial NOT N= ULL, > =E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82uniqueid varchar(6= 0) NULL, > =E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82username varchar(6= 0) NULL, > =E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82starttime timestam= p NULL, > =E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82stoptime timestamp= NULL > ) > PARTITION BY RANGE (starttime) > ; > ALTER TABLE testpart OWNER TO postgres; > CREATE TABLE part1 PARTITION OF testpart ( > =E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82CONSTRAINT part1_u= niqueid_key UNIQUE (uniqueid), > =E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82CONSTRAINT part1_p= key PRIMARY KEY (id) > )FOR VALUES FROM ('2022-09-15 00:00:00') TO ('2022-09-21 00:00:00'); > ALTER TABLE part1 OWNER TO postgres; > ALTER TABLE testpart ADD CONSTRAINT uniqueid_const UNIQUE (uniqueid, sta= rttime); > INSERT INTO testpart > VALUES(2, 'Microsoft','hotline', now(), now() + interval '1' hour) > ON CONFLICT (uniqueid,starttime) > DO NOTHING; --- This gives Error The precise sequence you give here doesn't fail for me. However, this table has three different uniqueness constraints: there's part1_uniqueid_key on uniqueid alone, part1_pkey on id alone, and then uniqueid_const on uniqueid plus starttime. Your ON CONFLICT clause will only trap conflicts on the last one. It's an implementation detail whether that gets checked before or after the constraint on uniqueid alone. I don't really feel a need to make that better-defined, because what in the world is the use for a constraint on uniqueid plus starttime alongside a constraint on uniqueid alone? regards, tom lane