CODEWITHSUNDEEP
postgresqlsql-insertupsertunique-index
sev
sev

Reputation: 1822

Postgres upsert with 2 unique constraints

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;

Upvotes: 2

Views: 665

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247465

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.

Upvotes: 1

Related Questions