Any examples showing how to handle NULL db entries?

[ccrotty]

In trying to run the Get() or ScanRow() method of the pgxscan library, if any of the fields are NULL in database an error is returned. Is there a way to scan while accounting for possible NULL values?

Thanks Chris

[georgysavva]

Hey. Absolutely. Scany supports possible NULL values. Check out this doc section for details: https://pkg.go.dev/github.com/georgysavva/scany@v0.3.0/dbscan#hdr-NULLs_and_custom_types

If you still struggle to make it work, add all your code relative to scany (struct definition, call to scany library, SQL query) in the comment and I will help you.

[ccrotty]

Maybe I'm just using the library incorrectly then.

I have a dummy table with the following data

Using the following code I get this error from the pgxscan.Get()... Error: scany: scan row into struct fields: can't scan into dest[3]: cannot scan null into *string

type User struct  {
    Id                  int           `db:"id"`
    Name           string      `db:"name"`
    Updated      bool      `db:"updated"`
    Nickname    string     `db:"nickname"`
}

func main() {
    dburl := "postgres://postgres:xxxx@xx.xx.xx.xx:xxxx/metrics"
    ctx := context.Background()

    dbpool, err := pgxpool.Connect(ctx, dburl)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Failed to connect to database: %v\n", err)
        os.Exit(1)
    }

    fmt.Printf("Connection succeeded to %s\n", dburl)
    defer dbpool.Close()

    // Get a single row
    var user2 User

    strquery := `SELECT * from cctest.nulltest where id = 2`
    err = pgxscan.Get(ctx, dbpool, &user2, strquery)
    if err != nil {
        fmt.Printf("Error: %s\n", err)
    } else {
        fmt.Printf("Second User is %s\n", user2.Name)
    }
}

[georgysavva]

In your struct definition the optional field should be defined as a pointer:

type User struct  {
        ...
    Nickname    *string    `db:"nickname"`
        ...
}

You can find more explanation about this in the docs section I sent you earlier.

[benedictjohannes]

@georgysavva I was (re)using your library for another project I'm handling, and I checked the issues tab to make sure there're no significant issues.

Allow me to suggest that you should close this issue, because this is not a problem related to scany, it's the implementing code (the way Go's native Scan function also err when scanning SQL NULL into non-pointer receiver). A lot of issues in your library is related to Q&A type issues like 75, which should be closed when an "answer comment" is provided.

[georgysavva]

@benedictjohannes thanks for your notice, I will do that!