How do UPSERT with on conflict two or more constraints?-CodePudding

I have a table:

I want insert or update data, that I get from another table. My constrains are three fields: id_1, id_2 and date. It's fields must be unique together. If I do:

ALTER TABLE my_table
ADD CONSTRAINT constr_1 UNIQUE (id_1, id_2, date);

and then:

insert into my_table
(id_1, id_2, quantity, date)
values (1, null, 5, '2022-04-27'),   -- values I get another select request
       (null, 5, 5, '2022-04-27'),  -- this means that the values can be different
       (99, 85, 100, '2022-04-29')
ON CONFLICT (id_1, id_2, date)
DO Update
SET quantity = excluded.quantity

Constrains not work and I just insert new rows with the same values. How to insert in the absence of a record and update in its presence?

CodePudding user response：

PostgreSQL treats NULL as distinct value, therefore, you can have multiple NULL values in a column with a UNIQUE index. When you define a primary key or a unique constraint for a table, PostgreSQL automatically creates a corresponding UNIQUE index.
The solution is to create a unique index, with coalesce. In this example I have used coalesce(~, 0) which means that null and 0 are treated as the same thing. You might prefer to use another value, for example the maximum possible value of int which is 2147483648.Please not that we have to modify the ON CONFLICT list to match the index.

CREATE temp TABLE my_table (
    id_1 int,
    id_2 int,
    quantity numeric,
    mytable_date date
);

✓

CREATE UNIQUE INDEX
my_table_unique ON my_table
(coalesce(id_1,0), coalesce(id_2,0), coalesce(mytable_date,'1900-01-01'));

✓

INSERT INTO my_table (id_1, id_2, quantity, mytable_date)
    VALUES (1, NULL, 5, '2022-04-27'), (NULL, 5, 5, '2022-04-27'), (99, 85, 100, '2022-04-29')
ON CONFLICT (coalesce(id_1,0), coalesce(id_2,0), coalesce(mytable_date,'1900-01-01'))
    DO UPDATE SET
        quantity = excluded.quantity;

INSERT INTO my_table (id_1, id_2, quantity, mytable_date)
    VALUES (99, 85, 101, '2022-04-29')
ON CONFLICT (coalesce(id_1,0), coalesce(id_2,0), coalesce(mytable_date,'1900-01-01'))
    DO UPDATE SET
        quantity = excluded.quantity;

3 rows affected

1 rows affected

select * from my_table;

id_1 | id_2 | quantity | mytable_date
---: | ---: | -------: | :-----------
   1 | null |        5 | 2022-04-27  
null |    5 |        5 | 2022-04-27  
  99 |   85 |      101 | 2022-04-29

*db<>fiddle here74bf159a4d041c31fec5f)

CodePudding user response：

CREATE temp TABLE my_table (
    id_1 int,
    id_2 int,
    quantity numeric,
    mytable_date date
);

ALTER TABLE my_table
    ADD CONSTRAINT constr_1 UNIQUE (id_1, id_2, mytable_date);

then

INSERT INTO my_table (id_1, id_2, quantity, mytable_date)
    VALUES (1, NULL, 5, '2022-04-27'), (NULL, 5, 5, '2022-04-27'), (99, 85, 100, '2022-04-29')
ON CONFLICT (id_1, id_2, mytable_date)
    DO UPDATE SET
        quantity = excluded.quantity;

INSERT INTO my_table (id_1, id_2, quantity, mytable_date)
    VALUES (99, 85, 101, '2022-04-29')
ON CONFLICT (id_1, id_2, mytable_date)
    DO UPDATE SET
        quantity = excluded.quantity;

please check manual section: 5.4.3. Unique Constraints about special case: null.

In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are never considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.