如何合并(更新插入和删除孤儿行)到tableA？
tableA：
+---------+--------+----------+-------+
| company | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | f | d | 2 | *
+---------+--------+----------+-------+
| a | g | e | 3 | **
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
| d | f | d | 1 |
+---------+--------+----------+-------+
*表示孤立行*。**表示要更改的值 (3->4)。
只接触tableB中存在的公司(示例中a和c，不要管d)。
tableB：
+---------+--------+----------+-------+
| company | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | g | e | 4 |
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
两个表中的(company, option, category)都有一个唯一的索引。
期望的结果tableA：
+---------+--------+----------+-------+
| company | option | category | rates |
+---------+--------+----------+-------+
| a | f | null | 2.5 |
+---------+--------+----------+-------+
| a | g | e | 4 | <-
+---------+--------+----------+-------+
| c | g | e | 4 |
+---------+--------+----------+-------+
| d | f | d | 1 |
+---------+--------+----------+-------+
只有第二行(a,f,d,2)被删除，rates从3更改为4以(a,g,e)。
这是一个小提琴：https://rextester.com/QUVC30763
我想先用这个删除孤儿行：
DELETE from tableA
USING tableB
WHERE
-- ignore rows with IDs that don't exist in tableB
tableA.company = tableB.company
-- ignore rows that have an exact all-column match in tableB
AND NOT EXISTS
(select * from tableB
where tableB.company is not distinct from tableA.company
AND tableB.option is not distinct from tableA.option
AND tableB.category is not distinct from tableA.category );
然后用这个更新插入：
INSERT INTO tableA (company, option, category, rates)
SELECT company, option, category, rates
FROM tableB
ON CONFLICT (company, option, category)
DO update
set rates= EXCLUDED.rates
WHERE
tableA.rates IS DISTINCT FROM
EXCLUDED.rates;
但是 upsert 函数的问题在于它无法处理可为空的字段。我必须设置-1来代替null否则函数将无法知道是否有重复项。我觉得用-1代替null将来会创建许多解决方法，所以如果可以的话，我想避免这种情况。
注意：我发现INSERT ... ON CONFLICT ... DO UPDATE可能是要走的路：
- 在PostgreSQL 9.5上进行合并
但是我还没有看到适合我的情况的查询。而且我不确定可空字段是否可行。因此问题来了：
有没有一种干净的方法可以与可为空的字段合并？
我认为你走在正确的道路上。但是NULL与UNIQUE：
option和category列可以NULL。在这些情况下，NULL被认为是平等的。您当前的唯一索引不认为NULL值相等，因此不强制实施您的要求。这甚至在您开始合并之前就会产生歧义。NULL值对您尝试实现的内容没有好处。解决此问题将产生更多的工作和额外的故障点。考虑使用特殊值而不是NULL，一切都会到位。你在考虑-1.任何对您的实际数据类型和属性性质自然有意义的内容。
也就是说，DELETE还有一个额外的、微妙隐藏的问题：它会尝试删除孤立行的次数，就像tableB中company上的匹配项一样多。由于过度尝试什么都不做，所以不会中断，但它是不必要的昂贵。请改用EXISTS两次：
DELETE FROM tableA a
WHERE EXISTS (
SELECT FROM tableB b
WHERE a.company = b.company
)
AND NOT EXISTS (
SELECT FROM tableB b
WHERE (a.company, a.option, a.category) IS NOT DISTINCT FROM
(b.company, b.option, b.category)
);
如果您坚持使用NULL值，则将 UPSERT 拆分为UPDATE，后跟INSERT ... ON CONFLICT DO NOTHING将是解决方法。如果您没有对表的并发写入，则更简单、更便宜。ON CONFLICT DO NOTHING无需指定冲突目标即可工作，因此您可以使用多个部分索引实现您的要求并使其正常工作。手册：
对于
ON CONFLICT DO NOTHING，可以选择指定一个conflict_target;省略时，与所有可用约束冲突 (和唯一索引)被处理。对于ON CONFLICT DO UPDATE，a 必须提供conflict_target。
但是，如果您使用有效的UNIQUE索引或约束来修复架构，则您已经拥有的 UPSERT 可以很好地发挥作用。
并确保没有并发写入表，否则您可能会面临竞争条件和/或死锁，除非您执行更多操作......