Postgresql - NULL 的解释类型错误

Question

我遇到以下 CTE 表达式的问题，因为 new_values 中的 prev_count 被解释为 text，但我正在更新的列counts 中的类型是 integer。我在标记的行上收到此错误:

ERROR:  column "prev_count" is of type integer but expression is of type text
LINE 12:     prev_count = new_values.prev_count

这是查询:

WITH  
    new_values (word,count,txid,prev_count) AS (
        VALUES ('cat',1,5,NULL)),
    updated AS (
        UPDATE 
            counts t 
        SET 
            count = new_values.count, 
            txid = new_values.txid, 
            prev_count = new_values.prev_count -- ERROR HERE
        FROM 
            new_values 
        WHERE (
            t.word = new_values.word
        ) 
    RETURNING t.*) 
INSERT INTO counts(
    word,count,txid,prev_count
) SELECT 
    word,count,txid,prev_count FROM new_values 
WHERE NOT EXISTS (
    SELECT 1 FROM updated WHERE (updated.word = new_values.word))

我的问题是，修复错误的优雅方法是什么？我宁愿在 new_values 中指定 prev_count 的类型，而不是添加显式强制转换，但我在文档中没有看到类似的内容。

Answer 1

在此处将其添加为明确的答案以及详细的解释。

修复是:

WITH  
    new_values (word,count,txid,prev_count) AS (
        VALUES ('cat',1,5,NULL::text)),

如评论中建议的 a_horse_with_no_name。

为什么这是必要的？因为行规范来自 VALUES 部分并且 NULL 是未知的。在这种情况下，PostgreSQL 有助于转换为文本。但这不是您想要的，因此您必须为 NULL 指定一个类型。

这在其他情况下也经常出现，例如 UNION 语句，其中列列表中第一段中的 NULL 可以被赋予隐式类型，该隐式类型与另一段中的列类型冲突部分。所以这是一个值得了解的棘手角落。