为了账号安全,请及时绑定邮箱和手机立即绑定

Postgres:使用 NULLIF 时获取“...超出整数类型的范围”

Postgres:使用 NULLIF 时获取“...超出整数类型的范围”

Go
胡说叔叔 2022-10-24 15:04:45
对于上下文,这个问题发生在我使用默认 postgres 数据库驱动程序编写的 Go 程序中。我一直在构建一个服务来与一个 postgres 数据库对话,该数据库有一个类似于下面列出的表:CREATE TABLE object (    id SERIAL PRIMARY KEY NOT NULL,    name VARCHAR(255) UNIQUE,    some_other_id BIGINT UNIQUE    ...);我为这个项目创建了一些端点,包括一个“安装”端点,它有效地充当了一个 upsert 函数,如下所示: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 objectSET some_other_id = COALESCE(NULLIF($2, 0), object.some_other_id)WHERE name = $1问题:每当我运行更新查询时,我总是遇到错误,引用字段“some_other_id”:pq:值“1010101010144”超出整数类型的范围但是,即使该行已经存在于数据库中(当它必须评估 COALESCE 语句时),此错误也不会发生在查询的“upsert”版本上。通过将 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语句中的类型混淆了?在我的代码和数据库之间没有存储过程要求一个INTEGERarg,至少我已经写过。
查看完整描述

2 回答

?
莫回无

TA贡献1865条经验 获得超7个赞

这与 postgres 解析器如何解析参数类型有关。我不知道它是如何实现的,但考虑到观察到的行为,我会假设INSERT查询不会失败,因为很明显(name,some_other_id) VALUES ($1,$2)参数应该与目标列$2具有相同的类型,即 type 。然后,此类型信息也用于查询部分的表达式。some_other_idint8NULLIFDO UPDATE SET

您还可以通过使用(name) VALUES ($1)in来测试此假设INSERT,您将看到 in 中的NULLIF表达式随后将以与查询DO UPDATE SET中相同的方式失败。UPDATE

因此UPDATE查询失败,因为没有足够的上下文供解析器推断$2参数的准确类型。解析器可以用来推断类型的“最接近”的东西$2NULLIF调用表达式,特别是它使用调用表达式的第二个参数的类型,即0类型为int4,然后它使用该类型信息第一个论点,即$2

为避免此问题,您应该对无法准确推断类型的任何参数使用显式类型转换。即使用NULLIF($2::int8, 0).


查看完整回答
反对 回复 2022-10-24
?
心有法竹

TA贡献1866条经验 获得超5个赞

COALESCE(NULLIF($51, CAST(0 AS BIGINT)), object.some_other_id)

五十一?真的吗?

pq:值“1010101010144”超出整数类型的范围

请注意,错误消息中的数据类型是integer,而不是bigint

我认为错误的原因是显示代码不足于是我拿出一个魔法水晶球,用手传球

一个“安装”端点,它像这样有效地充当一个 upsert 函数

我还有一个“更新”端点

您是否将端点称为PostgreSQL 函数(存储过程)?我想是的。另外 $1, $2 看起来像 PostgreSQL 函数参数。

魔法水晶球说:您有两个具有不同数据类型参数的 PostgreSQL 函数:

  1. “安装”端点具有 $2 函数参数作为bigint数据类型。看起来像CREATE FUNCTION Install(VARCHAR(255), bigint)

  2. “更新”端点具有 $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


查看完整回答
反对 回复 2022-10-24
  • 2 回答
  • 0 关注
  • 138 浏览
慕课专栏
更多

添加回答

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信