如何在唯一索引中合并两个可能为空值的表？

Question

如何将（upsert和delete孤立行）合并到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     |
+---------+--------+----------+-------+

+---------+--------+----------+-------+
| company | option | category | rates |
+---------+--------+----------+-------+
| a       | f      | null     | 2.5   |
+---------+--------+----------+-------+
| a       | g      | e        | 4     |
+---------+--------+----------+-------+
| c       | g      | e        | 4     |
+---------+--------+----------+-------+

+---------+--------+----------+-------+
| company | option | category | rates |
+---------+--------+----------+-------+
| a       | f      | null     | 2.5   |
+---------+--------+----------+-------+
| a       | g      | e        | 4     | <-
+---------+--------+----------+-------+
| c       | g      | e        | 4     |
+---------+--------+----------+-------+
| d       | f      | d        | 1     |
+---------+--------+----------+-------+

这里有一把小提琴: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 );

然后用这个Ussert：

 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;

null

Answer 1

我认为你走在正确的道路上。但是null与unique存在设计问题：

列选项和类别可以为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)
   );

并确保没有对表的并发写操作，否则您可能会面临竞争条件和/或死锁，除非您做更多...