主页 / dba / 问题 / 292428
Accepted
Carlos Marmolejo
Asked: 2021-05-29 08:15:21 +0800 CST

带有 NOT NULL 列的 PostgreSQL UPSERT 问题

  • 772

我想知道当其中一列（column1 或 column2）为空时是否可以进行 upsert，我只需要部分更新，当然，我试图避免SELECT验证记录是否已经存在。

这是我正在努力实现的一个例子。

CREATE TABLE test(
    column1 timestamp NOT NULL,
    column2 uuid,
    status integer NOT NULL,
    id uuid NOT NULL,
    CONSTRAINT upsert_conflict UNIQUE (id, status)
);

我有这样的事情：

INSERT INTO test(column1, status, id) 
VALUES(now(), 0, <any uuid>)
ON CONFLICT ON CONSTRAINT upsert_conflict 
DO UPDATE SET column1= now()

这个按预期工作，但我也有这样的事情：

INSERT INTO test(column2, status, id) 
VALUES(gen_random_uuid (), 0, <existing uuid>)
ON CONFLICT ON CONSTRAINT upsert_conflict 
DO UPDATE SET column2= gen_random_uuid ()

我知道这INSERT是错误的，因为我们无法将 NULL 值设置为 column1，但假设我已经有一条具有该 id 和状态的记录，我想这样做UPDATE，而不是那样，我得到一个错误：...violates not-null constraint.因为缺少的列。

我正在根据用户传递给我的内容动态创建这些查询。有时我会有类似第一个示例的内容，有时会有类似第二个示例的内容，而且我不知道哪一列不能为空。

我希望你能帮助我。

postgresql upsert

1 个回答

  1. Best Answer

    你的第二个例子从来没有意义，而column1is defined NOT NULL。您必须为所需列提供一个值，否则该命令将在它甚至可以检查唯一违规之前失败。

    查询 1

    此查询仅在其中一个或为 NULL 并且至少一个实际更改（任何实际更改）时才执行该UPDATE部分，但它不允许将任何一个恢复为 NULL：column1column2

    INSERT INTO test AS t
       (column1, column2          , status, id) 
VALUES (now()  , gen_random_uuid(), 0     , '5beb004a-63ec-4f01-8604-37296a208e5f')
ON     CONFLICT ON CONSTRAINT upsert_conflict
DO     UPDATE
SET    column1 = COALESCE(t.column1, EXCLUDED.column1)
     , column2 = COALESCE(t.column2, EXCLUDED.column2)
WHERE (t.column1 IS NULL
    OR t.column2 IS NULL)
AND   (t.column1 IS DISTINCT FROM COALESCE(t.column1, EXCLUDED.column1)
    OR t.column2 IS DISTINCT FROM COALESCE(t.column2, EXCLUDED.column2));

    但从COALESCE()不参与这个特定的设置。column1, 被定义NOT NULL，NULL既不能在表中，也不能在输入中VALUES。所以column2 必须为 NULL 否则什么也不会发生。因此，我们永远无法column2重置NULL。

    查询 2

    此查询仅在其中一个或为 NULL 并且至少有一个实际上正在更改时才执行该UPDATE部分- 即使返回为 NULL：column1column2

    INSERT INTO test AS t
       (column1, column2, status, id) 
VALUES (now()  , NULL   , 0     , '5beb004a-63ec-4f01-8604-37296a208e5f')
ON     CONFLICT ON CONSTRAINT upsert_conflict
DO     UPDATE
SET    column1 = EXCLUDED.column1
     , column2 = EXCLUDED.column2
WHERE (t.column1 IS NULL
    OR t.column2 IS NULL)
AND   (t.column1 IS DISTINCT FROM EXCLUDED.column1
    OR t.column2 IS DISTINCT FROM EXCLUDED.column2);

    这一次，使用实际的输入值，即使是 NULL。否COALESCE()，如果新值是，则默认为现有值NULL

    db<>在这里摆弄

    无论哪种方式，您都可以确保(status, id) = (0, '5beb004a-63ec-4f01-8604-37296a208e5f')在成功执行命令后存在一行 - 即使该行可能从RETURNING子句返回的集合中丢失。看：

    有关的：

    • 1

相关问题