RowToStructByName doesn't play nice with nullable fields (oapi-codegen/nullable)

[artis3n]

I am using https://github.com/oapi-codegen/nullable to define a struct where some fields may be explicitly set to null in the DB. My struct:

type MyThing struct {
    Id          int                         `db:"id"`
    FirstThing  nullable.Nullable[string]   `db:"first"`
    NextThing   nullable.Nullable[[]string] `db:"next"`

https://pkg.go.dev/github.com/oapi-codegen/nullable#Nullable

It's a really useful convenience when unmarshalling JSON structs. This is my first time trying to use nullable.Nullable with a database struct object.

I then try to pull out the data with:

q, err := conn.Query(ctx, "SELECT * FROM mytable")
if err != nil {...}
results, err := pgx.CollectRows(q, pgx.RowToStructByName[MyThing])

The RowToStructByName fails with the following error:

can't scan into dest[1]: cannot scan _text (OID 25) in binary format into *nullable.Nullable[string]

Am I just trying to do something incompatible with pgx? It doesn't look like #2188 or #1434 are similar enough to what I'm trying to do, but I may not be understanding.

[jackc]

Those types do not implement any database/sql or pgx interfaces so pgx has no way of knowing what to do with them. It's not just RowToStructByName, it wouldn't work with a normal Query / Scan either.

Presumably, you cannot modify that library so implementing the proper interfaces is not an option. However, pgx does have a way to support 3rd party types that cannot be modified. See https://github.com/jackc/pgx-shopspring-decimal for an example of how to support a 3rd party type. It can be a bit of work though. The adapter for shopspring/decimal is about 300 LOC. Start with the Register function if you want to explore the code.

Also, I doubt it can be done generically, so you would need to write the adapter code for each concrete type (e.g. nullable.Nullable[string] and nullable.Nullable[float64]).

[artis3n]

That is really helpful, thanks! I was going to start digging into options next week. I will definitely take a look at that. I think my first attempt will be to set the struct to a regular string and add omitempty, and use Scan to cast the column record into a Nullable myself after pgx extracts it.