How to use arrays with v5/stdlib

[regeda]

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?

[jackc]

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}]
``

[regeda]

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.

[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.

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 #1781 for an example.

[regeda]

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)
}

[gspeicher]

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)
}

[kirk-anchor]

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.

[jackc]

Candidate solution: #2020