PostgreSQL ON CONFLICT DO UPDATE 带有非空列和 COALESCE EXCLUDED 列

Question

我希望能够执行包含非空约束冲突的数据的UPSERT操作，但不会引发错误。 我希望在出现冲突时跳过该行而不是报错。

模式：

               Table "public.appointments"
    Column   |            Type             |  Modifiers
-------------+-----------------------------+--------------
 id          | bigint                      | not null
 location_id | bigint                      | not null
 time        | timestamp without time zone | not null
 status      | text                        | not null
Indexes:
    "appointments_pkey" PRIMARY KEY, btree (id)
    "for_upsert" UNIQUE CONSTRAINT, btree (id, location_id)

现有记录：

 id | location_id | status |       time         
----+-------------+--------+----------------------
  1 |           2 | sched  | 2017-12-15 01:10:00
  2 |           2 | sched  | 2017-12-19 01:30:00

查询：

INSERT INTO "objects" ("id", "location_id", "time")
  VALUES (1, 2, 'sched', '2017-10-31 19:25:00'),
         (2, 2, 'canc', NULL),
         (3, 2, 'canc', NULL)
  ON CONFLICT ON CONSTRAINT for_upsert DO UPDATE
    SET "time" = COALESCE(EXCLUDED."time", "objects"."time"),
        "status" = EXCLUDED."status"

当记录存在且time不为空时，我希望无论EXCLUDED."time"的值是什么（这就是COALESCE尝试实现的功能），都能更新status。但如果没有现有记录并且新提供的行具有空time，则我不希望插入该行或引发错误。

- Reed G. Law

Answer 1

添加此条款：

    WHERE COALESCE(EXCLUDED."time", "appointments"."time") IS NOT NULL;

在执行 DO UPDATE SET... 之后似乎可以工作。

- Reed G. Law

---

这在新值上没有起作用（即当插入的time值一开始就是空时）。为了解决这个问题，我创建了一个AFTER触发器来删除无效的行。 - Reed G. Law