Pgx: can't scan into dest[0]: cannot assign 0 into int

[Zamiell]

Today, pgx gives me can't scan into dest[0]: cannot assign 0 into int, which seems to be a pretty vague error message.

I don't think I'm doing anything particularly interesting, just:

SELECT COUNT(id) FROM users WHERE user_id = $1

and Scanning that into a Golang int.

I already tried changing scanning into a Golang int32 and a Golang int64, but still got the same error.

Any tips? If needed, I can try to put together a working test-case or something.

[jackc]

That's a pretty basic case that should work fine. If you provide a test case I can look at it.

[Zamiell]

I ended up just refactoring my SQL and moving as much logic as I could out of the SQL and into Golang code. Not sure if that is best-practice or not, but the problem went away after the refactor. I probably won't go back and create a test-case, so I'll close this now, thanks.

[Kieren]

I've been troubleshooting a similar issue, where I received a slightly different error (pgx.scanArgError): can't scan into dest[0]: cannot assign 0 1 into *uint

In my case I was trying to scan into a Golang uint. The error was thrown on a NULL source value from a particular row in the query result. I tracked the source of the thrown error down to /jackc/pgtype/convert.go line: 209, func int64AssignTo(srcVal int64, srcStatus Status, dst interface{}) error declaration. It seems that that particular function doesn't attempt to assign or deal with a NULL source value specifically at all.

In the error message the '0' after 'cannot assign' corresponds to the formatted source value of NULL and the '1' is the pgtype.Status value of 1 (Null).

I worked around my issue by coalescing the value returned in the query.
Hope this helps.

[Zamiell]

That indeed sounds like it could have been my problem.

[edit] Actually, on second thought, I don't see how a COUNT(id) could possibly return a NULL. It would either return 0 or a number.

Anyways, at worst, pgx should have a better error message, if what Kieren is saying is true.

[janishorsts]

probably variable passed by value instead of by reference

[stigok]

Same issue for me. It turned out to be an empty query result set that triggered this error.

failed to get funds for user bandit1337: pgx.scanArgError{col:0, err:(*xerrors.noWrapError)(0x100cd60)}
can't scan into dest[0]: cannot assign 0 1 into *int32

Thank you for your tips, @Kieren! For reference, coalescing the return value would make the query look like this:

var funds int32

row := db.QueryRow(ctx,
    "SELECT COALESCE(SUM(value), 0) FROM transactions WHERE username = $1 AND value",
    "bandit1337")

if err := row.Scan(&funds); err != nil {
    log.Printf("failed to get funds for user %s: %#v", u.Username, err)
    log.Print(err)
} 

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display [...]

https://www.postgresql.org/docs/12/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL

[In-Flight]

Same issue for me. It turned out to be an empty query result set that triggered this error.

failed to get funds for user bandit1337: pgx.scanArgError{col:0, err:(*xerrors.noWrapError)(0x100cd60)}
can't scan into dest[0]: cannot assign 0 1 into *int32

I has same error for []int64 when table row in postgres like arr bigint[] NOT NULL DEFAULT ARRAY[NULL::bigint] and value {NULL}:

can't scan into dest[5]: cannot assign 0 1 into *int64

I resolve this error:

import (
    ...
    "github.com/jackc/pgtype"
)
...
arr, tmp := []int64{}, pgtype.Int8Array{}
err := row.Scan(&tmp)
if tmp.Elements[0].Status == pgtype.Present {
    tmp.AssignTo(&arr)
}

[MatthewJamesBoyle]

I'm facing the same issue:
DB Schema:

create table users
(
    id serial not null
    constraint users_pkey
    primary key,
    email varchar not null,
    first_name varchar not null,
    last_name varchar not null,
);

Code:

func (a store) GetUserIDForEmail(ctx context.Context, email string) (int, error) {
       userQuery := `SELECT id FROM users WHERE email = $1`
    row, err := a.db.Query(ctx, userQuery, email)
    if err != nil {
        return -1, fmt.Errorf("failed to save token: %w", err)
    }
    defer row.Close()

    if row.Err() != nil {
        return -1, fmt.Errorf("failed to save token: %w", err)
    }
    if !row.Next() {
        return -1, errUnauthorized
    }

    var userID int
    if err := row.Scan(userID); err != nil {
        return -1, err
    }

    return userID, nil
}

Leads to:
Error: can't scan into dest[0]: cannot assign 95 into int.
I have tried:

• using an int32,int64,uint,uint32,uint64 and string
• using an sql.NullInt32

All yield the same error.

[janishorsts]

var userID int
if err := row.Scan(userID); err != nil {
return -1, err
}

Do not pass userID variable by value, Scan() requires a pointer so to change value
row.Scan(&userID)

[MatthewJamesBoyle]

Thanks for the fast response and of course it is, serves me right for programming at 3 am :). however I wonder if its worth improving the error messaging around this to say it needs to be a pointer.