postgresql 使用合并功能插入非空列时忽略单个空值

1l5u6lss 于 2023-03-12 发布在 PostgreSQL

关注(0)|答案(1)|浏览(230)

我想构造一个partial insert-on-conflict-update语句，它可以为非空列取空值，只要更新的行有空值，这样就不会违反约束;在多列的情况下，我希望每个列都被更新，即使其中一个为空，然后被忽略。
当使用coalesce(excluded.value, table.value)更新值时，我希望该语句接受null，因为将使用现有值，满足约束，但是该语句以错误结束，指出违反了非null约束。
为了测试它，这里有一个MWE：

CREATE TABLE test (id INT NOT NULL, val INT NOT NULL, PRIMARY KEY (id));
INSERT INTO test VALUES (0, 1);

INSERT INTO test
VALUES (0, 2)
ON CONFLICT (id)
DO UPDATE SET val=COALESCE(EXCLUDED.val, test.val);
SELECT * FROM test;

INSERT INTO test
VALUES (0, null)
ON CONFLICT (id)
DO UPDATE SET val=COALESCE(EXCLUDED.val, test.val);
SELECT * FROM test;

这里有一个SQL Fiddle来做同样的事情。
所以我认为在VALUES中已经检查了约束，而不是在SET中。有什么方法可以实现这一点吗？

postgresql

来源：https://stackoverflow.com/questions/75673891/ignore-individual-nulls-when-upserting-into-non-null-columns-with-coalesce

1条答案

按热度按时间

goqiplq21#

我不知道我是否理解的很清楚，你永远不能把一个空值插入到一个非空值中，但是如果你想检查返回另一个值，那么你可以在插入时直接使用COALESCE函数。
要进行插入，您可以始终在插入中选中它：

INSERT INTO test
VALUES (1, COALESCE(null, 2));
select * from test;

CONFLICT表示插入已存在的主键或唯一键：

INSERT INTO test
VALUES (0, 1)
ON CONFLICT (id)
DO UPDATE SET val=COALESCE(EXCLUDED.val, test.val);
select * from test;

INSERT INTO test (id, val)
VALUES (0, 1)
ON CONFLICT (id) DO UPDATE
SET id=test.id+1, val=test.val;
select * from test;

这是一个SQL错误
否则，您将不得不使用触发器来更新它，因为正如@Atmo向您解释的那样，DB会按照以下顺序进行更新：
1.检查要插入的记录上的约束（后面的查询在此处失败）
1.检查冲突
1.如果没有冲突则插入记录，或者如果有冲突则更新现有记录。

赞(0）回复(0）举报 2023-03-12

我来回答

postgresql 使用合并功能插入非空列时忽略单个空值

1条答案

相关问题

热门标签

最新问答