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

probably variable passed by value instead of by reference

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.

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

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)

I’m not sure if “not supporting NULL values” is expected behavior for a database driver. All pgx have to do is assuming the missing data is the default value of target variable.

There can be a big difference in meaning between NULL and the zero value. pgx provides NULL supporting types through the github.com/jackc/pgtype package.

However, I agree that it is convenient to sometimes treat NULL and the zero value the same. Check out the github.com/jackc/pgtype/zeronull package. That includes types for common types that automatically convert NULL to zero and vice versa.

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.

In case it helps anyone - my issue was that I was trying to scan into the struct itself instead of the struct members, ie. rows.Scan(&event) instead of rows.Scan(&event.Name).

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