使用数组字段将postgres行转换为golang结构

Question

I am having postgres db table as

CREATE TABLE foo (
name varchar(50),
types varchar(50)[],
role varchar[10]
);

and corresponding struct in go:

type Foo struct {
Name string `db:"name"`
Types []string `db:"types"`
Role string `db:"role"`
}

I want to fetch db rows into my struct. Right now I am able to do this by using:

var foo Foo
query := `SELECT name, types, roles FROM foo LIMIT 1`
err = dbConn.QueryRow(query).Scan(&foo.Name,  pq.Array(&foo.Types), &foo.Role)

But I want to achieve the same using direct mapping. Something like:

var foo []Foo
query := `SELECT name, types, roles FROM foo`
dbWrapper.err = dbConn.Select(&foo, query)

Above snippet gives me error because of Types being pq array. Is it possible to directly map pq array as a part of struct?

Answer 1

Thanks to https://stackoverflow.com/a/44385791/10138004, I am able to solve this pq driver for sqlx (https://godoc.org/github.com/lib/pq) itself by replacing []string with pq.StringArray.

So, updated struct looks like:

type Foo struct {
Name string `db:"name"`
Types pq.StringArray `db:"types"` //this is what changed.
Role string `db:"role"`
}

and direct mapping is working like a charm now

var foo []Foo
query := `SELECT name, types, roles FROM foo`
dbWrapper.err = dbConn.Select(&foo, query)