对于上下文,此问题发生在我使用默认 postgres 数据库驱动程序编写的 Go 程序中。
我一直在构建一个服务来与 postgres 数据库通信,该数据库有一个类似于下面列出的表:
CREATE TABLE object (
id SERIAL PRIMARY KEY NOT NULL,
name VARCHAR(255) UNIQUE,
some_other_id BIGINT UNIQUE
...
);
我为这个项目创建了一些端点,包括一个"安装"端点,它有效地充当更新插入函数,如下所示:
INSERT INTO object (name, some_other_id)
VALUES ($1, $2)
ON CONFLICT name DO UPDATE SET
some_other_id = COALESCE(NULLIF($2, 0), object.some_other_id)
我还有一个"更新"端点,其中包含如下所示的底层查询:
UPDATE object
SET some_other_id = COALESCE(NULLIF($2, 0), object.some_other_id)
WHERE name = $1
问题:
每当我运行更新查询时,我总是会遇到错误,引用字段"some_other_id":
pq:值"1010101010144"超出了整数类型的范围
但是,此错误永远不会发生在查询的"upsert"版本上,即使该行已存在于数据库中(当它必须计算 COALESCE 语句时)。我已经能够通过将 COALESCE 语句更新为如下来防止此错误:
COALESCE(NULLIF($2, CAST(0 AS BIGINT)), object.some_other_id)
但是,由于第一个查询从未发生过,我想知道这种不一致是否来自我做错了什么或不理解的事情?还有最佳实践是什么,我应该铸造所有值吗?
我肯定会将 64 位整数传递给"some_other_id"的查询,即使没有显式类型转换,第一个查询也适用于 Go 实现。
如果需要更多信息(或Go实现),请告诉我,提前非常感谢!(:
编辑:
为了消除混淆,查询直接在 Go 代码中执行,如下所示:
res, err := s.db.ExecContext(ctx, `UPDATE object SET some_other_id = COALESCE(NULLIF($2, 0), object.some_other_id) WHERE name = $1`,
"a name",
1010101010144,
)
这两个查询以完全相同的方式执行。
编辑:还更正了我当前解决方法中的参数(从$51
到$2
)。
我还想借此机会指出,该查询确实适用于我提出的修复程序,这表明该问题出在我将 postgres 与NULLIF
语句中的类型混淆?没有存储过程要求在我的代码和数据库之间提供INTEGER
参数,至少我已经编写了。
这与 postgres 解析器如何解析参数的类型有关。我不知道它是如何实现的,但考虑到观察到的行为,我会假设INSERT
查询不会失败,因为从(name,some_other_id) VALUES ($1,$2)
可以清楚地看出,$2
参数应该与目标some_other_id
列具有相同的类型,即int8
类型。然后,此类型信息也用于查询DO UPDATE SET
部分的NULLIF
表达式中。
您还可以通过在INSERT
中使用(name) VALUES ($1)
来测试此假设,您将看到DO UPDATE SET
中的NULLIF
表达式将以与UPDATE
查询相同的方式失败。
因此,UPDATE
查询失败,因为没有足够的上下文供分析器推断$2
参数的准确类型。解析器可以用来推断$2
类型的"最接近"的东西是NULLIF
调用表达式,具体来说,它使用调用表达式的第二个参数的类型,即0
,它是类型int4
的,然后它使用该类型信息作为第一个参数,即$2
.
若要避免此问题,应将显式类型强制转换与无法准确推断类型的任何参数一起使用。 即使用NULLIF($2::int8, 0)
.
COALESCE(NULLIF($51, CAST(0 AS BIGINT)), object.some_other_id)
五十一?真的吗?
pq:值"1010101010144"超出了整数类型的范围
请注意,错误消息中的数据类型是整数,而不是bigint。
我认为错误的原因是显示的代码。于是我拿出一个魔法水晶球,用手传球。
一个"安装"端点,它有效地充当更新插入函数,如下所示
我还有一个"更新"端点
您是否将端点称为PostgreSQL函数(存储过程)?我认为是的。 同样 $1,$2 看起来像 PostgreSQL 函数参数。
魔水晶球说:你有两个具有不同数据类型参数的PostgreSQL函数:
"安装"端点将 $2 函数参数作为bigint数据类型。看起来像
CREATE FUNCTION Install(VARCHAR(255), bigint)
"更新"端点将 $2 函数参数作为整数数据类型,而不是bigint。看起来像
CREATE FUNCTION Update(VARCHAR(255), integer)
.
最后,我会重写你的条件更容易理解:
UPDATE object
SET some_other_id =
CASE
WHEN $2 = 0 THEN object.some_other_id
ELSE $2
END
WHERE name = $1