psycopg3中使用DO $$块实现时间版本控制表的UPSERT时遇到参数数据类型无法确定的错误，如何解决？

嗨，我来帮你搞定这个问题！你碰到的psycopg.errors.IndeterminateDatatype错误，核心原因是PL/pgSQL的匿名DO块没有显式的参数声明，psycopg没办法从代码上下文自动推断绑定参数（比如你的xml参数）的数据类型，所以数据库才会抛出“无法确定$2参数类型”的报错。

下面给你两种可行的解决思路，其中第二种更推荐，符合PostgreSQL的最佳实践：

方案一：修复DO块的参数类型问题 #

我们可以在DO块内部先声明明确类型的变量，把绑定参数赋值给这些变量，让数据库能清晰识别参数类型。修改后的代码如下：

QUERY = """
DO $$
DECLARE
    p_retailer_id integer := %(retailer_id)s;
    p_xml text := %(xml)s;
BEGIN
    IF EXISTS (
        SELECT 1 
        FROM retailer_xml 
        WHERE retailer_id = p_retailer_id AND expire_tstamp IS NULL
    ) THEN
        UPDATE retailer_xml
        SET expire_tstamp = CURRENT_TIMESTAMP
        WHERE retailer_id = p_retailer_id AND expire_tstamp IS NULL;
    END IF;

    INSERT INTO retailer_xml (retailer_id, xml)
    VALUES (p_retailer_id, p_xml);
END $$;
"""

通过显式声明integer和text类型的变量，PostgreSQL就能准确判断参数类型，不会再触发类型推断错误了。

方案二：改用WITH语句实现UPSERT（推荐） #

你之前尝试的WITH方法其实更简洁高效，之前报错唯一约束大概率是因为写错了表名（你代码里写的是remap_list_xml，但实际表是retailer_xml），修正后就能正常运行：

QUERY = """
WITH expired AS (
    UPDATE retailer_xml
    SET expire_tstamp = CURRENT_TIMESTAMP
    WHERE retailer_id = %(retailer_id)s AND expire_tstamp IS NULL
    RETURNING retailer_xml_id
)
INSERT INTO retailer_xml (retailer_id, xml)
VALUES (%(retailer_id)s, %(xml)s);
"""

这个逻辑的好处是：不管有没有找到需要过期的活跃行，都会执行插入操作。而且因为更新后旧行的expire_tstamp不再是NULL，新插入的行（expire_tstamp默认NULL）完全符合你设置的部分唯一索引规则，不会触发约束冲突。

另外还要确认一下：你传入的retailer_id是整数类型，xml是字符串类型，这样参数绑定的时候才不会有类型不匹配的问题哦。

备注：内容来源于stack exchange，提问作者Viet Than