sql - Postgresql:仅在提供新值时才进行冲突更新

Question

我有这张表:

CREATE TABLE myTable (
  a VARCHAR(32),
  b VARCHAR(32) DEFAULT NULL,
  c VARCHAR(32) DEFAULT NULL,

  PRIMARY KEY (a)
);

和这个“UPSERT”类型的查询:

INSERT INTO 
  myTable ( a,  b,  c)
  VALUES  ($1, $2, $3)
ON CONFLICT (a)
  DO UPDATE SET
    b = $2,
    c = $3,
RETURNING
  a, b, c
;

这按预期工作。如果我首先插入(为了符号方便而使用 json):

{a:"a", b:"b", c:"c"}

然后我可以用这样的参数更新它:

{a:"a", b:"x", c:"y"}

我得到了预期的结果——{a:"a"} 记录更新了 b 和 c 列。

但我也希望能够做到这一点:

{a:"a", c:"Q"}

并更新 c 列同时保持 b 列不变。

当然，我需要右手边的某种表达式，但我不知道那是什么。我的直觉是这样的(SQL 伪代码):

...
  DO UPDATE SET
    b = ($2 | b)
    c = ($3 | c)
...

这里正确的语法是什么？或者，我应该使用完全不同的方法吗？

Answer 1

您可以使用 COALESCE()。这个想法是将 NULL 值传递给其中一个参数，然后您可以:

INSERT INTO myTable (a,  b,  c)
VALUES  ($1, $2, $3) 
DO UPDATE SET
    b = COALESCE(EXCLUDED.b, b)
    c = COALESCE(EXCLUDED.c, c)

当一条记录已经存在并且 a = $1 和 $1 或 $2 被赋予一个 null 值和 ，则仅写入非 null 值。

缺点是此查询无法将 null 值分配给已存在的记录。

旁注:这使用伪表 EXCLUDED 而不是重复参数。