Postgres upsert with 2 unique constraints

Question

I have a table with 2 unique indexes to allow for null values in one of the columns.

CREATE TABLE public.pairs_dup
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    base integer NOT NULL,
    quote integer NOT NULL,
    delivery_date timestamp with time zone,
    data float,
    CONSTRAINT pairs_dup_pkey PRIMARY KEY (id)
)

CREATE UNIQUE INDEX pairs_dup_not_null_idx
    ON public.pairs_dup USING btree
    (base ASC NULLS LAST, quote ASC NULLS LAST, delivery_date ASC NULLS LAST)
    WHERE delivery_date IS NOT NULL;

CREATE UNIQUE INDEX pairs_dup_null_idx
    ON public.pairs_dup USING btree
    (base ASC NULLS LAST, quote ASC NULLS LAST)
    WHERE delivery_date IS NULL;

A) Can I perform an upsert when providing a delivery_date?

INSERT INTO pairs_dup(base, quote, delivery_date, data) values ('A', 'B', '2016-06-22 19:10:25-07', 0.5)
ON CONFLICT (base, quote, delivery_date) DO UPDATE SET data = 0.5;

B) Can I perform an upsert without providing a delivery date?

INSERT INTO pairs_dup(base, quote, data) values ('A', 'B', 0.5)
ON CONFLICT (base, quote, delivery_date) DO UPDATE SET data = 0.5;

Laurenz Albe · Accepted Answer · 2021-06-22 00:25:32Z

1

Ad A): Yes, but the ON CONFLICT clause needs to contain the index predicate:

INSERT INTO pairs_dup(base, quote, delivery_date, data)
   VALUES (42, 4711, '2016-06-22 19:10:25-07', 0.5)
ON CONFLICT (base, quote, delivery_date)
   WHERE delivery_date IS NOT NULL  /* this is essential */
   DO UPDATE SET data = 0.5;

As the documentation says:

index_predicate

Used to allow inference of partial unique indexes. Any indexes that satisfy the predicate (which need not actually be partial indexes) can be inferred.

Ad B): Again, you have to add WHERE delivery_date IS NULL to the ON CONFLICT clause.

Adding these clauses seems redundant, but it is required so that a partial index can be inferred.

answered Jun 22, 2021 at 0:25

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres upsert with 2 unique constraints

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related