在冲突上如何努力两个或多个约束？

Question

我有一个表：

我想从另一个表中获取插入或更新数据。我的约束是三个字段：ID_1，ID_2和日期。它的字段必须在一起是唯一的。 如果我这样做：

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

然后：

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

约束不起作用，我只是插入具有相同值的新行。如何在没有记录和更新的情况下插入？

Answer 1

PostgreSQL将零视为独特的值，因此，您可以在具有唯一索引的列中具有多个空值。当您为表定义主键或唯一约束时，PostgreSQL会自动创建相应的唯一索引。
解决方案是与Colesce创建一个独特的索引。在此示例中，我使用了cocce（〜，0），这意味着Null和0被视为同一件事。您可能更喜欢使用另一个值，例如int的最大可能值，即2147483648。请不要修改冲突列表以匹配索引。

 创建temp表my_table（
    id_1 int，
    id_2 int，
    数量数字，
    mytable_date日期
）；
 

✓

 创建唯一的索引
my_table_unique my_table
（cocce（id_1,0），cocece（id_2,0），cocece（mytable_date，'1900-01-01'））;
 

✓

 插入my_table（ID_1，ID_2，数量，mytable_date）
    值（1，Null，5，'2022-04-27'），（Null，5，5，'2022-04-27'），（99，85，100，'2022-04-29'）
关于冲突（Cocce（ID_1,0），CoceCe（ID_2,0），Cocece（Mytable_date，'1900-01-01'））
    做更新集
        数量=排除。

插入my_table（ID_1，ID_2，数量，mytable_date）
    值（99、85、101，'2022-04-29'）
关于冲突（Cocce（ID_1,0），CoceCe（ID_2,0），Cocece（Mytable_date，'1900-01-01'））
    做更新集
        数量=排除。
 

3行影响

1行影响

 从my_table中选择 *;
 

 id_1 | ID_2 |数量| mytable_date
---：| ---：| -------：| ：-----------------
   1 |  null  | 5 | 2022-04-27  
 null  | 5 | 5 | 2022-04-27  
  99 | 85 | 101 | 2022-04-29  

*db＆lt; EC5F）

Answer 2

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);

然后，

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;

请检查手册部分： 5.4.3。独特的约束关于特殊情况： null 。

通常，如果有多个
表中的行，所有包含的所有列的值
约束是相等的。但是，两个无效的值永远不会
在此比较中被认为是平等的。即使在存在
一个独特的约束，可以存储重复行
在至少一个约束列中包含一个空值。这
行为符合SQL标准，但我们听说过其他
SQL数据库可能不遵循此规则。因此，请小心
旨在便携的开发应用程序。