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
