pgx
pgx copied to clipboard
jackc
How to use arrays with v5/stdlib
The documentation declares the following:
ArrayCodec implements support for arrays. If pgtype supports type T then it can easily support []T by registering an ArrayCodec for the appropriate PostgreSQL OID. In addition, Array[T] type can support multi-dimensional arrays.
I'm trying to use the following:
type X struct {
ids []pgtype.UUID
}
func smth(x X) error {
// "conn" is established using `v5/stdlib`
_, err := conn.ExecContext("....$1....", x.ids)
return err
}
However, I'm getting the following:
sql: converting argument $1 type: unsupported type []pgtype.UUID, a slice of struct
How to use arrays in both ways Valuer and Scanner?
You can pass Go arrays directly as query arguments. For scanning you need to use pgtype.Map.SQLScanner.
package main
import (
"database/sql"
"log"
"os"
"github.com/jackc/pgx/v5/pgtype"
_ "github.com/jackc/pgx/v5/stdlib"
)
func main() {
db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
if err != nil {
log.Fatal(err)
}
defer db.Close()
m := pgtype.NewMap()
uuidsIn := []pgtype.UUID{
{Bytes: [16]byte{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}, Valid: true},
{Bytes: [16]byte{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}, Valid: true},
}
var uuidsOut []pgtype.UUID
err = db.QueryRow("select $1::uuid[]", uuidsIn).Scan(m.SQLScanner(&uuidsOut))
if err != nil {
log.Fatal(err)
}
log.Println(uuidsOut)
}
Output:
2023/10/26 20:38:15 [{[1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 0] true} {[1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 0] true}]
``
Got it!
@jackc Do you have any plans to avoid using SQLScanner in future? It makes the data model layer not interoperable if different SQL drivers are in place.
Do you have any plans to avoid using SQLScanner in future? It makes the data model layer not interoperable if different SQL drivers are in place.
pgx supports []T as long as it understands T. But database/sql requires the type implement sql.Scanner. The only options I know of are using something like SQLScanner or creating a specific type for each array that implements sql.Scanner. See https://github.com/jackc/pgx/issues/1781 for an example.
Maybe the following code could help smbd:
type Array[T any] []T
func (a *Array[T]) Scan(src any) error {
m := pgtype.NewMap()
v := (*[]T)(a)
t, ok := m.TypeForValue(v)
if !ok {
return fmt.Errorf("cannot convert to sql.Scanner: cannot find registered type for %T", a)
}
var bufSrc []byte
if src != nil {
switch src := src.(type) {
case string:
bufSrc = []byte(src)
case []byte:
bufSrc = src
default:
bufSrc = []byte(fmt.Sprint(bufSrc))
}
}
return m.Scan(t.OID, pgtype.TextFormatCode, bufSrc, v)
}
Thanks @regeda, this works great for pgtype.FlatArray[T] and pgtype.Multirange[T] because both are type aliases for []T which represents a builtin type. In case anyone is trying to make it work with pgtype.Range[T], the following works. A similar approach should work for pgtype.Array.
type Range[T any] struct {
pgtype.Range[T]
}
func (a *Range[T]) Scan(src any) error {
m := pgtype.NewMap()
v := (*pgtype.Range[T])(&a.Range)
t, ok := m.TypeForValue(v)
if !ok {
return fmt.Errorf("cannot convert to sql.Scanner: cannot find registered type for %T", a)
}
var bufSrc []byte
if src != nil {
switch src := src.(type) {
case string:
bufSrc = []byte(src)
case []byte:
bufSrc = src
default:
bufSrc = []byte(fmt.Sprint(bufSrc))
}
}
return m.Scan(t.OID, pgtype.TextFormatCode, bufSrc, v)
}
I have queries that use jmoiron/sqlx so I cannot use pgtype.Map.SQLScanner on arrays. I was previously using the type specific array structs on pgx-v4. I managed to workaround the issue by using to_jsonb(my_array) in the query, scanning it to a []byte and then calling json.Unmarshal() to parse it to the Go type.
