How to scan an array that can be null?

[kalensk]

I feel like this is such an easy question, but after much searching including looking at the unit tests and trying I'm still needing help.

I have a postgres field of NAME[] that can be entirely NULL which I would like to scan into a golang []string or similar type. Note, that my field can be NULL.

My code is something like the following:

	var output []string
	err := sql.QueryRow(`select '{"foo", "NULL", " NULL "}'::name[];`).Scan(&output)
	if err != nil {
		return err
	}

which errors with: unsupported Scan, storing driver.Value type string into type *[]string. I tried fiddling with *[]string, pgtype.Array[string] and pgtype.FlatArray[string] and such.

[jackc]

That should work using the native pgx v5 interface. However, since you are using database/sql / stdlib you will need to use pgtype.Map.SQLScanner().

Something like (untested, but I think it should work):

map := pgtype.NewMap() // this can be reused, but it is not concurrency safe

var output []string
err := sql.QueryRow(`select '{"foo", "NULL", " NULL "}'::name[];`).Scan(map.SQLScanner(&output))
if err != nil {
		return err
}

[kalensk]

Perfect! That worked well.

A couple of followup questions that I hope you can point me in the right direction.

1. How might this example look if one were to use pgx v5 directly and not use database/sql?

2. Are there other more complex examples that show how to scan into a complex struct? Or how to best learn using pgx?
   Basically, I want to scan into the following struct. And insert values into a table from this struct.

type MyTable struct {
	Id                  int64
	DateCreated         time.Time
	Table        table.Table                  
	AlgorithmType       algorithm_type.AlgorithmType 
	Name           string              
	Accuracy            float32
	OutputTables results.OutputTables
	Label           string              
}

[jackc]

1. You don't need the map.SQLScanner.

conn.QueryRow(`select '{"foo", "NULL", " NULL "}'::name[];`).Scan(&output)

2. There are several examples in the docs such as https://pkg.go.dev/github.com/jackc/pgx/v5#example-RowToStructByName.