Cannot use a variable of type `int64` with `NULLIF`

[aliml92]

I have the following query:

UPDATE big_numbers 
    SET value = COALESCE(NULLIF($2, 0), value)
WHERE id = $1 
RETURNING id, value 

I am using the type bigint in PostgreSQL and int64 in go for value column. When try to update the column, I am getting the error message below:

pq: value "2147483648" is out of range for type integer

Interestingly, it works if update it with "2147483647" which is the maximum value of int32.
Full code, main.go:

package main

import (
    "context"
    "database/sql"
    "fmt"

    _ "github.com/lib/pq"
)

const createTable = `CREATE TABLE IF NOT EXISTS big_numbers (id serial PRIMARY KEY, value bigint)`

const insert = `INSERT INTO big_numbers (value) VALUES ($1) RETURNING id`

const update = `
UPDATE big_numbers 
    SET value = COALESCE(NULLIF($2, 0), value)
WHERE id = $1 
RETURNING id, value 
`

type BigNumber struct {
    ID    int    `json:"id"`
    Value int64 `json:"value"`
}

func main() {

    dsn := "host=localhost port=5435 user=demouser password=demopassword dbname=demodb sslmode=disable"
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    ctx := context.Background()

    // create table
    _, err = db.Exec(createTable)
    if err != nil {
        panic(err)
    }

    // insert initial data
    var id int64
    err = db.QueryRowContext(ctx, insert, 10).Scan(&id)
    if err != nil {
        panic(err)
    }

    // update data (works)
    newValue := int64(2147483647)
    u, err := UpdateUser(ctx, db, id, newValue)  // 2147483647 is the max value for int32
    if err != nil {
        fmt.Println(err)
    }
    fmt.Printf("Updated: %v\n", u)

    // update data (fails)
    newValue = int64(2147483648)
    _, err = UpdateUser(ctx, db, id, newValue)  
    if err != nil {
        fmt.Printf("Failed to update: %v\n", err)
    }
}

func UpdateUser(ctx context.Context, db *sql.DB, id int64, value int64) (BigNumber, error) {
    row := db.QueryRowContext(ctx, update, id, value)
    var b BigNumber
    err := row.Scan(&b.ID, &b.Value)
    return b, err
}

And docker-compose.yaml file:

services:
  postgres:
    image: postgres
    environment:
      POSTGRES_DB: demodb
      POSTGRES_USER: demouser
      POSTGRES_PASSWORD: demopassword
    ports:
      - "5435:5432"
    expose:
      - "5432"    
    restart: unless-stopped

Any help works!

[cbandy]

I'm not finding a page that explains, but this can happen with query parameters. The fix is usually to add a type cast.

NULLIF($2::bigint, 0)

[aliml92]

@cbandy, It worked, thank you!