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.

[aflynn93]

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

[ufukty]

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.

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. So, if a DATE area is NULL and target variable's type is string , then assume it like it is the string "". Another solution could be letting developer to choose default values in .Scan() call. I am not sure if my expectation is same for everyone tho.

[jackc]

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.

[kakhavk]

The simple solution was to add ::INTEGER after field err = conn.QueryRow(context.Background(), "select name, weight::INTEGER from widget where id=$1", 42).Scan(&name, &weight)

[CodeNinjaUG]

None of the solutions has worked for me this is ma query

query := INSERT INTO orders(items, total, address, customer_id, status, created_at) VALUES(@orderItems, @orderTotal, @orderAddress, @orderCustomer, @orderStatus, @orderCreatedAt) RETURNING id, total, customer_id; VALUES(@orderItems, @orderTotal, @orderAddress, @orderCustomer, @orderStatus, @orderCreatedAt) RETURNING id, total, customer_id;` trans := pgx.NamedArgs{ "orderItems": items, "orderTotal": float64(grandtotal), "orderAddress": address, "orderCustomer" : id, "orderStatus" : "Pending", "orderCreatedAt" : time.Now(), }
///execution err := database.DB.QueryRow(context.Background(),query, trans).Scan(&order.Id, &order.Total, &order.Customer) if err != nil{ println(err) c.Status(500) return c.JSON(fiber.Map{ "error" : err, }) }

//orderstruct type Order struct{ Id int Items []Item Total float64 Customer int Address Address CreatedAt time.Time UpdatedAt time.Time Status orderstatus }

any help is apprecitated

[sergej-brazdeikis]

I had the same error and I solved it!

Basically, it is typo level error. In my case it was chaning:

row.Scan(myvar)

to

.Scan(&myvar)

Yes, it was that simple :)

@MatthewJamesBoyle you should try row.Scan(&userID) ;)