Postgres 12批量更新timestamp字段时类型不匹配报错求助

你在Postgres 12中执行批量更新created_at字段的SQL时遇到如下错误：

ERROR: column "created_at" is of type timestamp with time zone but expression is of type text

原因是Django的DateTimeField(auto_now_add=True)对应Postgres的timestamp with time zone类型，但你在VALUES子句中传入的时间串被Postgres默认识别为text类型，两种类型无法直接隐式转换，因此触发报错。

解决方法 #

有两种可行的修正方式：

方式一：显式指定VALUES子句的字段类型

在VALUES的每个值后添加类型声明，明确告诉Postgres时间串的类型是timestamp with time zone：

UPDATE foo_bar AS c SET
created_at = c2.created_at
FROM (VALUES
    (101::int, '2021-09-27 14:54:00.0+00'::timestamp with time zone),
    (153::int, '2021-06-02 14:54:00.0+00'::timestamp with time zone)
) as c2(id, created_at)
WHERE c.id = c2.id;

方式二：在赋值时显式转换类型

如果不想逐个标记值的类型，可以在赋值语句中对c2.created_at进行类型转换：

UPDATE foo_bar AS c SET
created_at = c2.created_at::timestamp with time zone
FROM (VALUES
    (101, '2021-09-27 14:54:00.0+00'),
    (153, '2021-06-02 14:54:00.0+00')
) as c2(id, created_at)
WHERE c.id = c2.id;

注意事项 #

确保传入的时间串包含时区信息（比如示例中的+00），这样转换后的值才会和Django DateTimeField的存储逻辑一致。

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