如何在不列出每一列的情况下，使用Postgres打开冲突更新原始表中的NULL列

Question

我正在阅读一个大的数据列表，并将其添加到PostgreSQL数据库中。问题是，有时我正在读取的数据中有重复的值，但有时它们会填充以前丢失的数据。为了弥补这一点，我在脚本中添加了以下内容，但它非常丑陋：

INSERT INTO tab(id,col1,col2,col3,...) VALUES (i,v1,v2,v3,...)
ON CONFLICT (id)
DO UPDATE 
SET 
(col1,col2,col3, ...)=(
COALESCE(tab.col1, EXCLUDED.col1),
COALESCE(tab.col2, EXCLUDED.col2),
COALESCE(tab.col3, EXCLUDED.col3),
...
);

我希望有一个比手动写出表中的每一列更优雅的解决方案。我还有几个表需要写，所以我更希望有一种更通用的方法来做这件事。

编辑：我对这件事还很陌生，读过这些文档，这可能是一种愚蠢的方式。请让我知道我是否应该为此使用INSERT命令，看起来可能只有UPDATE或某种形式的JOIN可以完成同样的事情？

Postgres版本：psql (PostgreSQL) 12.14 (Ubuntu 12.14-0ubuntu0.20.04.1)

Answer 1

需要列出每一列，但不需要手动输入列表。以下查询是使用information_schema中的列信息生成SET子句的示例：

WITH query_fragments AS (
SELECT
string_agg(quote_ident(c.column_name), ', ' ORDER BY c.ordinal_position) AS column_list,
string_agg(format('COALESCE(tab.%I, excluded.%I)', c.column_name, c.column_name), ', ' ORDER BY c.ordinal_position) AS column_values
FROM
information_schema.columns c
WHERE
c.table_schema = 'public'
AND c.table_name = 'tab'
AND c.column_name <> 'id'
)
SELECT
format('SET (%s) = (%s)', column_list, column_values) AS set_clause
FROM
query_fragments;

这可以很容易地合并到一个函数中，并进行扩展以生成每个表的整个插入查询。

Answer 2

您的查询看起来基本不错。正如John构建它的元查询一样。

一个主要问题仍然存在：不要更新实际上没有更改的行。这就产生了完全的更新成本，而没有任何收益。

INSERT INTO tab AS t
(id, col1, col2, col3)
VALUES (i , v1  , v2  , v3  )
ON     CONFLICT (id) DO UPDATE 
SET   (col1, col2, col3) =
(COALESCE(t.col1, EXCLUDED.col1),
COALESCE(t.col2, EXCLUDED.col2),
COALESCE(t.col3, EXCLUDED.col3))
WHERE  EXCLUDED IS DISTINCT FROM t;

更好(但更详细)：

...
WHERE (col1, col2, col3) IS DISTINCT FROM 
(COALESCE(t.col1, EXCLUDED.col1),
COALESCE(t.col2, EXCLUDED.col2),
COALESCE(t.col3, EXCLUDED.col3));     -- !!!

第一个建议仅在输入行与现有行完全相同的情况下抑制更新
第二个(更好)建议将抑制所有空更新。

相关：

• 如何(或可以)在多列上选择DISTINCT
• 零值的质量合并
• 在plpgsql中更新触发器函数中的多列
• 更新以特定字符串开头的多列

高级方法

我的上述建议有助于最大限度地减少昂贵的更新次数
如果您可以在不增加太多开销的情况下进行管理，那么就不要重复更新同一行在应用UPDATE之前，将多个输入行合并为一行。

类似：

INSERT INTO tab AS t
(id, col1   , col2   , col3)
SELECT i , min(v1), min(v2), min(v3)
FROM   my_input_rows i
GROUP  BY 1
ON     CONFLICT (id) DO UPDATE 
SET   (col1, col2, col3) =
(COALESCE(t.col1, EXCLUDED.col1),
COALESCE(t.col2, EXCLUDED.col2),
COALESCE(t.col3, EXCLUDED.col3))
WHERE (col1, col2, col3) IS DISTINCT FROM 
(COALESCE(t.col1, EXCLUDED.col1),
COALESCE(t.col2, EXCLUDED.col2),
COALESCE(t.col3, EXCLUDED.col3));

您可以先向TEMPORARY暂存表my_input_rows写入内容，然后从中获取它。或者像在初始代码中那样使用VALUES表达式，只是不直接附加到INSERT，因此可能需要显式类型转换。参见：

• 更新多行时强制转换NULL类型

我选择了min()，因为它忽略了null的输入。如果每组有null或一个不同值，那就可以了。

甚至可以在一条SQL语句中对多个目标表执行此操作，使用多个数据修改CTE。