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

user_5959293 0
Viewed 335

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.

go sql-insert sql-update postgresql
2 Answers

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

user_9659000 
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
user_166861300
Related