Python批量更新PostgreSQL数值列为NULL时类型不匹配报错问题

这个报错的核心原因是：当你传入None（对应SQL里的NULL）时，PostgreSQL在解析临时表data的qoh列类型时，没有足够的上下文推断它是numeric类型，默认把它当成了text类型，而目标表items的qoh列是numeric，类型不兼容导致报错。

这里有两种简单有效的解决方法：

方法一：明确指定临时表列的类型 #

在定义临时表data时，直接给每个列指定对应的类型，尤其是qoh列要明确为NUMERIC，这样PostgreSQL就会正确识别NULL的类型。修改后的代码如下：

update_query = "UPDATE items SET qoh = data.qoh FROM (VALUES %s) " \
"AS data ( company_id INT, item_id INT, qoh NUMERIC ) " \
"WHERE items.company_id = data.company_id " \
"AND items.item_id = data.item_id"

data = [(2, 1, 10)]
psycopg2.extras.execute_values(self.cursor, update_query, data)
data = [(2, 1, None)]
psycopg2.extras.execute_values(self.cursor, update_query, data)

临时表data的qoh列被明确标记为NUMERIC后，传入的NULL就会被识别为numeric类型的空值，和目标列类型匹配，不会再报错。

方法二：显式转换列类型 #

如果你不想修改临时表的定义，也可以在UPDATE的赋值语句里，把data.qoh显式转换为NUMERIC类型，代码修改如下：

update_query = "UPDATE items SET qoh = data.qoh::NUMERIC FROM (VALUES %s) " \
"AS data ( company_id, item_id, qoh ) " \
"WHERE items.company_id = data.company_id " \
"AND items.item_id = data.item_id"

data = [(2, 1, 10)]
psycopg2.extras.execute_values(self.cursor, update_query, data)
data = [(2, 1, None)]
psycopg2.extras.execute_values(self.cursor, update_query, data)

通过::NUMERIC的类型转换，不管data.qoh原本被推断成什么类型，都会被转换成numeric，从而和目标列兼容。

这两种方法都能解决你遇到的类型不匹配问题，推荐第一种，因为它更清晰地定义了数据结构，避免后续可能出现的其他类型问题。

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