postgresql 如何在不列出每一列的情况下使用Postgres ON CONFLICT更新原始表中的NULL列？

fkaflof6 于 2023-04-05 发布在 PostgreSQL

关注(0)|答案(2)|浏览(168)

我正在阅读一个很大的数据列表，并将其添加到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)

postgresql

来源：https://stackoverflow.com/questions/75893439/how-to-on-conflict-update-null-columns-in-the-original-table-using-postgres-with

2条答案

按热度按时间

3xiyfsfu1#

需要列出每一列，但不需要手动输入列表。以下查询是使用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;

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

赞(0）回复(0）举报 2023-04-05

avwztpqn2#

你的查询看起来很好，构建它的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));     -- !!!

第一个建议仅在输入行与现有行完全相同的情况下禁止更新。
第二个（better）建议会抑制所有空更新。
相关：

如何（或可以）对多列执行SELECT 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 staging table my_input_rows，然后从那里获取它。或者像初始代码一样使用VALUES表达式，只是不直接附加到INSERT，所以你可能需要显式的类型转换。请参阅：

更新多行时转换NULL类型

我选择了min()，因为它忽略了null输入。如果你有null或每个集合有 * 一个 * 不同的值，那就可以了。
甚至可以在一个SQL语句中对多个目标表执行此操作，使用多个数据修改CTE。

赞(0）回复(0）举报 2023-04-05

我来回答

postgresql 如何在不列出每一列的情况下使用Postgres ON CONFLICT更新原始表中的NULL列？

2条答案

上级法

相关问题

热门标签

最新问答