public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Inzamam Shafiq <[email protected]>
Cc: 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 02:41:29 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <AM9P251MB0330200D3E9C4875862B10E898489@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM>
References: <AM9P251MB033090199F1A3C2B1742A50A98499@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM>
	<CAJexoS+F37aQStLD4chj51EhtLyUWFNTtC4Vduq4XqR_bFwYCQ@mail.gmail.com>
	<AM9P251MB0330200D3E9C4875862B10E898489@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM>

Inzamam Shafiq <[email protected]> writes:
> 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

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

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], [email protected]
  Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
  In-Reply-To: <[email protected]>

* 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