Postgres:在使用NULLIF时出现“…超出整数类型范围”的错误。

huangapple go评论96阅读模式
英文:

Postgres: getting "... is out of range for type integer" when using NULLIF

问题

为了提供上下文,这个问题发生在我正在使用默认的postgres数据库驱动程序编写的Go程序中。

我正在构建一个与postgres数据库通信的服务,该数据库具有类似下面列出的表:

CREATE TABLE object (
    id SERIAL PRIMARY KEY NOT NULL,
    name VARCHAR(255) UNIQUE,
    some_other_id BIGINT UNIQUE
    ...
);

我为这个项目创建了一些端点,包括一个"Install"端点,它实际上充当了一个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"端点,其底层查询如下所示:

UPDATE object
SET some_other_id = COALESCE(NULLIF($2, 0), object.some_other_id)
WHERE name = $1

问题是:

每当我运行更新查询时,我总是遇到错误,引用字段"some_other_id":

> pq: value "1010101010144" is out of range for type integer

然而,即使在数据库中已经存在行(当它必须评估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)。

我还想借此机会指出,使用我的提议修复,查询确实可以工作,这表明问题在于我在NULLIF语句中混淆了postgres的类型?在我的代码和数据库之间没有要求INTEGER参数的存储过程,至少我没有编写过。

英文:

For context, this issue occurred in a Go program I am writing using the default postgres database driver.

I have been building a service to talk to a postgres database which has a table similar to the one listed below:

CREATE TABLE object (
    id SERIAL PRIMARY KEY NOT NULL,
    name VARCHAR(255) UNIQUE,
    some_other_id BIGINT UNIQUE
    ...
);

I have created some endpoints for this item including an "Install" endpoint which effectively acts as an upsert function like so:

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)

I also have an "Update" endpoint with an underlying query like so:

UPDATE object
SET some_other_id = COALESCE(NULLIF($2, 0), object.some_other_id)
WHERE name = $1

The problem:

Whenever I run the update query I always run into the error, referencing the field "some_other_id":

> pq: value "1010101010144" is out of range for type integer

However this error never occurs on the "upsert" version of the query, even when the row already exists in the database (when it has to evaluate the COALESCE statement). I have been able to prevent this error by updating COALESCE statement to be as follows:

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

But as it never occurrs with the first query I wondered if this inconsitency had come from me doing something wrong or something that I don't understand? And also what the best practice is with this, should I be casting all values?

I am definitely passing in a 64 bit integer to the query for "some_other_id", and the first query works with the Go implementation even without the explicit type cast.

If any more information (or Go implementation) is required then please let me know, many thanks in advance! (:

Edit:

To eliminate confusion, the queries are being executed directly in Go code like so:

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,
)

Both queries are executed in exactly the same way.

Edit: Also corrected parameter (from $51 to $2) in my current workaround.

I would also like to take this opportunity to note that the query does work with my proposed fix, which suggests that the issue is in me confusing postgres with types in the NULLIF statement? There is no stored procedure asking for an INTEGER arg inbetween my code and the database, at least that I have written.

答案1

得分: 1

这与Postgres解析器如何解析参数的类型有关。我不知道具体的实现方式,但根据观察到的行为,我认为INSERT查询不会失败是因为从(name,some_other_id) VALUES ($1,$2)可以明确知道$2参数应该与目标some_other_id列具有相同的类型,即int8类型。这个类型信息也被用在查询的DO UPDATE SET部分的NULLIF表达式中。

你可以通过在INSERT中使用(name) VALUES ($1)来测试这个假设,你会发现在UPDATE查询中DO UPDATE SETNULLIF表达式会以与UPDATE查询相同的方式失败。

所以UPDATE查询失败是因为解析器没有足够的上下文来推断出$2参数的准确类型。解析器可以用来推断$2类型的最接近的东西是NULLIF调用表达式,具体来说,它使用调用表达式的第二个参数的类型,即0,它是int4类型,然后将该类型信息用于第一个参数,即$2

为了避免这个问题,你应该在任何类型无法准确推断的参数上使用显式类型转换。例如,使用NULLIF($2::int8, 0)

英文:

This has to do with how the postgres parser resolves types for the parameters. I don't know how exactly it's implemented, but given the observed behaviour, I would assume that the INSERT query doesn't fail because it is clear from (name,some_other_id) VALUES ($1,$2) that the $2 parameter should have the same type as the target some_other_id column, which is of type int8. This type information is then also used in the NULLIF expression of the DO UPDATE SET part of the query.

You can also test this assumption by using (name) VALUES ($1) in the INSERT and you'll see that the NULLIF expression in DO UPDATE SET will then fail the same way as it does in the UPDATE query.

So the UPDATE query fails because there is not enough context for the parser to infer the accurate type of the $2 parameter. The "closest" thing that the parser can use to infer the type of $2 is the NULLIF call expression, specifically it uses the type of the second argument of the call expression, i.e. 0, which is of type int4, and it then uses that type information for the first argument, i.e. $2.

To avoid this issue, you should use an explicit type cast with any parameter where the type cannot be inferred accurately. i.e. use NULLIF($2::int8, 0).

答案2

得分: 0

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

五十一?真的吗?

>pq: 值 "1010101010144" 超出了整数类型的范围

请注意,错误信息中的数据类型是整数,而不是大整数

我认为错误的原因是显示的代码之外。所以我拿出了一个魔法水晶球,用手做了一个手势。
>一个 "Install" 端点,实际上充当了一个像这样的 upsert 函数

>我还有一个 "Update" 端点

你把端点称为PostgreSQL函数(存储过程)了吗?我想是的。
而且 $1、$2 看起来像是 PostgreSQL 函数的参数。

魔法水晶球说:你有两个 PostgreSQL 函数,它们的参数数据类型不同:

  1. "Install" 端点的 $2 函数参数是大整数数据类型。看起来像是 CREATE FUNCTION Install(VARCHAR(255), bigint)

  2. "Update" 端点的 $2 函数参数是整数数据类型,而不是大整数。看起来像是 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

<details>
<summary>英文:</summary>

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

Fifty-one? Realy?

>pq: value "1010101010144" is out of range for type integer

Pay attention, the data type in the error message is an integer, not bigint.

I think the reason for the error is out of showed code. So I take out a magic crystal ball and make a pass with my hands.
>an "Install" endpoint which effectively acts as an upsert function like so

>I also have an "Update" endpoint

Do you call endpoint a PostgreSQL function (stored procedure)? I think yes.
Also $1, $2 looks like PostgreSQL function arguments.

The magic crystal ball says: you have two PostgreSQL function with different data types of arguments:

  1. "Install" endpoint has $2 function argument as a bigint data type. It looks like CREATE FUNCTION Install(VARCHAR(255), bigint)

  2. "Update" endpoint has $2 function argument as an integer data type, not bigint. It looks like CREATE FUNCTION Update(VARCHAR(255), integer).

At last, I would rewrite your condition more understandable:

UPDATE object
SET some_other_id = 
CASE 
WHEN $2 = 0 THEN object.some_other_id
ELSE $2
END
WHERE name = $1

huangapple
  • 本文由 发表于 2022年1月5日 06:41:36
  • 转载请务必保留本文链接:https://go.coder-hub.com/70585867.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定