PostgreSQL更新numeric列全为NULL值报错原因与解决方案

现象原因 #

你碰到的报错是PostgreSQL的VALUES表达式自动类型推断逻辑导致的：
PostgreSQL在解析VALUES生成的派生表时，不会全量扫描所有行来判断每一列的数据类型，只会扫描前10行左右的样例数据进行推断。如果某一列扫描到的所有样例值都是NULL，PostgreSQL会默认将该列的类型判定为text，这就和你表中定义的numeric类型不匹配，触发类型不兼容报错。
你碰到的“即使有非NULL值也偶尔报错”的情况，就是刚好该列的前10行数据全为NULL，PostgreSQL完成类型推断后不会再扫描后面的行，哪怕后面有符合要求的numeric类型值，也会直接按text类型处理，所以抛出报错。

问题解决 #

基础修复方案（避免类型报错） #

最稳妥的方案是显式指定VALUES派生表的每一列类型，完全绕开PostgreSQL的自动类型推断逻辑，不需要修改数据处理逻辑，只要调整SQL写法即可，适配你当前的execute_values使用方式：

from psycopg2.extras import execute_values
sql = """UPDATE test as t SET
a = e.a,
b = e.b
FROM (VALUES %s) 
AS e(idx int, a numeric, b numeric) -- 这里显式指定每一列的类型，和业务表字段类型完全对齐
WHERE t.idx = e.idx"""
execute_values(cursor, sql, data)

你当前采用的赋值时转型e.a::numeric的写法也可以解决问题，但是字段多的时候容易漏写转型规则，更推荐上面显式指定派生表类型的写法。

大批次更新优化方案（适配百万级表场景） #

你当前的单次5万行更新的批次大小配合普通UPDATE已经可以满足大部分场景的性能要求，如果还需要进一步提升更新效率、降低锁等待和WAL日志压力，可以改用临时表+COPY的方案：

1. 先创建和更新字段类型完全对齐的临时表：

CREATE TEMP TABLE temp_update (
    idx int PRIMARY KEY,
    a numeric,
    b numeric
) ON COMMIT DROP;

2. 用psycopg2.copy_from接口把5万行数据批量写入临时表，该接口的写入性能比execute_values高30%以上
3. 用临时表和业务表关联完成更新：

UPDATE test t
SET a = temp.a, b = temp.b
FROM temp_update temp
WHERE t.idx = temp.idx;

该方案因为临时表的字段类型是创建时显式指定的，完全不会出现类型推断错误的问题，同时大批次写入的性能更高，适合长期业务使用。

内容的提问来源于stack exchange，提问作者xcosmos