public inbox for [email protected]  

help / color / mirror / Atom feed

From: Inzamam Shafiq <[email protected]>
To: Steve Midgley <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
Date: Fri, 16 Sep 2022 05:49:43 +0000
Message-ID: <AM9P251MB0330200D3E9C4875862B10E898489@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CAJexoS+F37aQStLD4chj51EhtLyUWFNTtC4Vduq4XqR_bFwYCQ@mail.gmail.com>
References: <AM9P251MB033090199F1A3C2B1742A50A98499@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM>
	<CAJexoS+F37aQStLD4chj51EhtLyUWFNTtC4Vduq4XqR_bFwYCQ@mail.gmail.com>

Following is the DDL,

CREATE TABLE testpart (
      id bigserial NOT NULL,
      uniqueid varchar(60) NULL,
      username varchar(60) NULL,
      starttime timestamp NULL,
      stoptime timestamp NULL
)
PARTITION BY RANGE (starttime)
;

ALTER TABLE testpart OWNER TO postgres;


CREATE TABLE part1 PARTITION OF testpart (
      CONSTRAINT part1_uniqueid_key UNIQUE (uniqueid),
      CONSTRAINT part1_pkey 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, starttime);

INSERT INTO testpart
VALUES(2, 'Microsoft','hotline', now(), now() + interval '1' hour)
ON CONFLICT (uniqueid,starttime)
DO NOTHING;  --- This gives Error

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "part1_uniqueid_key"
  Detail: Key (uniqueid)=(Microsoft) already exists.

INSERT INTO testpart
VALUES(2, 'Microsoft','hotline', now(), now() + interval '1' hour)
ON CONFLICT ON CONSTRAINT uniqueid_const
DO NOTHING; --This is equivalent to above statement


INSERT INTO part1
VALUES(3, 'Microsoft','hotline', now(), now() + interval '1' hour)
ON CONFLICT (uniqueid)
DO NOTHING; -- This works perfectly

Regards,

Inzamam Shafiq
Sr. DBA
________________________________
From: Steve Midgley <[email protected]>
Sent: Friday, September 16, 2022 4:09 AM
To: Inzamam Shafiq <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12



On Thu, Sep 15, 2022 at 6:32 PM Inzamam Shafiq <[email protected]<mailto:[email protected]>> 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

view thread (8+ messages)  latest in thread

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
  In-Reply-To: <AM9P251MB0330200D3E9C4875862B10E898489@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox