UPSERT查询不使用零值.如何在约束中使用零值更新

Question

我正在使用基于三列组合的冲突中的PGSQL UPSERT查询。在这里，一列可以为null是否为null，其他两个列将始终包含该值。如果所有三列都具有值，则UPSERT查询工作（更新值），如果其他可以为null具有值的值，则它是在插入记录而不是更新。

CREATE TABLE "public"."plan_line_items" (
    "plan_id" int4 NOT NULL
    "module_id" int4 NOT NULL,
    "sub_module_id" int4,
    "created_date" timestamptz(6) NOT NULL DEFAULT now(),
    "modified_date" timestamptz(6) NOT NULL DEFAULT now(),
    "created_by" int4 NOT NULL,
    "modified_by" int4 NOT NULL,
    "is_active" bool NOT NULL DEFAULT true,
);
INSERT INTO plan_line_items1 ( plan_id,module_id, sub_module_id, created_by, modified_by, modified_date )
VALUES ( '1', '1', '1', '36', '36', 'now()' ),
( '1', '2', null, '36', '36', 'now()' ) ON CONFLICT ( 
plan_id,module_id,sub_module_id ) DO UPDATE SET modified_by = EXCLUDED.modified_by,
modified_date = EXCLUDED.modified_date

我第一次尝试此查询时插入记录。之后，当我第二次尝试时，它仅更新具有所有plan_id，module_id，sub_module_id的记录。36'，'36'，'now（））。我尝试了很多事情，但没有找到任何方法。我想在第二次执行此查询（而不是插入）时更新该记录。如果有人有解决方案，请提供帮助。

Answer 1

您在某种程度上有些误解了NULL在您唯一约束的上下文中的含义（即使您在DDL中不存在，我认为您已经存在了）。

具有NULL值的sub_module_id并不意味着"此plan_line_item没有sub_module_id"，这意味着"我们不知道此plan_line_item是否具有sub_module_id"。这就是为什么它在您独特的约束中无法正常工作的原因。

我建议将sub_module_id列的数据类型更改为：

sub_module_id int4 not null default 0

...然后，对于那些没有sub_module_id的plan_line_item行，插入0的值，而不是NULL。因此：

CREATE TABLE regt.plan_line_items (
    plan_id int4 NOT null,
    module_id int4 NOT NULL,
    sub_module_id int4 not null default 0,
    created_date timestamptz(6) NOT NULL DEFAULT now(),
    modified_date timestamptz(6) NOT NULL DEFAULT now(),
    created_by int4 NOT NULL,
    modified_by int4 NOT NULL,
    is_active bool NOT NULL DEFAULT true,
    unique (plan_id,module_id,sub_module_id)
);
INSERT INTO regt.plan_line_items ( plan_id,module_id, sub_module_id, created_by, modified_by, modified_date )
VALUES ( '1', '1', '1', '36', '36', 'now()' ),
( '1', '2', '0', '36', '36', 'now()' ) ON CONFLICT ( 
plan_id,module_id,sub_module_id ) DO UPDATE SET modified_by = EXCLUDED.modified_by,
modified_date = EXCLUDED.modified_date;

或者，换句话说： NULL！= NULL