Cannot scan NULL into integer fields

[derekchiang]

If I have a struct with an integer field, and if the value in DB is NULL, scany returns an error like:

scany: scan row into struct fields: can't scan into dest[4]: cannot assign 0 1 into *uint64"

What would be a good workaround here? Is there any way to instruct scany to just put 0 into the field instead?

[georgysavva]

Hi, and thank you for this issue! Could you please provide a complete example of your struct, SQL query, and scany API call. Thanks!

[derekchiang]

Sure! Here's a minimal example.

Schema

CREATE TABLE IF NOT EXISTS sales (
    id SERIAL PRIMARY KEY,
    sold_to INTEGER
);

Struct

type Sale struct {
    ID                  uint64
    SoldTo              uint64
}

Query

    var sale Sale
    err := pgxscan.Get(ctx, db,
        &sale,
        `
        SELECT *
        FROM sales 
        WHERE id = $1
        `,
        1,
    )

Here if sold_to is NULL in the database, the query will fail.

[georgysavva]

If SoldTo is a NULLable field, it should be defined as *unit64:

type Sale struct {
    ID        uint64
    SoldTo    *uint64
}

Check this doc section for details: https://pkg.go.dev/github.com/georgysavva/scany@v0.2.8/dbscan#hdr-NULLs_and_custom_types.

Alternatively, you could use a custom pgtype type pgtype.Int8 instead of *uint64like that:

type Sale struct {
    ID        uint64
    SoldTo    pgtype.Int8
}

See pgtype docs for details: https://pkg.go.dev/github.com/jackc/pgtype?utm_source=godoc#Int8

[derekchiang]

If SoldTo is a NULLable field, it should be defined as *unit64:

type Sale struct {
  ID        uint64
  SoldTo    *uint64
}

Check this doc section for details: https://pkg.go.dev/github.com/georgysavva/scany@v0.2.8/dbscan#hdr-NULLs_and_custom_types.

Alternatively, you could use a custom pgtype type pgtype.Int8 instead of *uint64like that:

type Sale struct {
  ID        uint64
  SoldTo    pgtype.Int8
}

See pgtype docs for details: https://pkg.go.dev/github.com/jackc/pgtype?utm_source=godoc#Int8

Ah got it -- that's very useful to know! Thanks and I will close the issue for now.