Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12 - Mailing list pgsql-sql

Inzamam Shafiq <inzamam.shafiq@hotmail.com> 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