sqlc + pgx: array of user defined enum: cannot scan unknown type (OID 16385) in text format

Question

I'm using sqlc and pgx/v5, and getting the below error for a postgres array of a user defined enum type:

Error: can't scan into dest[1]: cannot scan unknown type (OID 16385) in text format into *pgtype.Array[my-app/sqlc.Option]

schema and query:

CREATE TYPE option AS ENUM (
    'OPT_1',
    'OPT_2',
    'OPT_3'
);

CREATE TABLE IF NOT EXISTS blah (
    id BIGINT PRIMARY KEY,
    options option[] NOT NULL DEFAULT '{OPT_1}'
);

-- name: CreateBlah :one
INSERT INTO blah (
    id
) VALUES (
    $1
)
RETURNING *;

sqlc appears to generate the types correctly:

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.16.0

package sqlc

import (
    "database/sql/driver"
    "fmt"

    "github.com/jackc/pgx/v5/pgtype"
)

type Option string

const (
    OptionOPT1 Option = "OPT_1"
    OptionOPT2 Option = "OPT_2"
    OptionOPT3 Option = "OPT_3"
)

func (e *Option) Scan(src interface{}) error {
    switch s := src.(type) {
    case []byte:
        *e = Option(s)
    case string:
        *e = Option(s)
    default:
        return fmt.Errorf("unsupported scan type for Option: %T", src)
    }
    return nil
}

type NullOption struct {
    Option Option
    Valid  bool // Valid is true if Option is not NULL
}

// Scan implements the Scanner interface.
func (ns *NullOption) Scan(value interface{}) error {
    if value == nil {
        ns.Option, ns.Valid = "", false
        return nil
    }
    ns.Valid = true
    return ns.Option.Scan(value)
}

// Value implements the driver Valuer interface.
func (ns NullOption) Value() (driver.Value, error) {
    if !ns.Valid {
        return nil, nil
    }
    return string(ns.Option), nil
}

func (e Option) Valid() bool {
    switch e {
    case OptionOPT1,
        OptionOPT2,
        OptionOPT3:
        return true
    }
    return false
}

type Blah struct {
    ID      int64
    Options pgtype.Array[Option]
}

I can work around it by defining my own type and implementing the scanner interface then specifying an override in the sqlc configuration:

package types

import (
    "fmt"
    "strings"

    "github.com/jackc/pgx/v5/pgtype"
)

type Options pgtype.Array[string] // <-- cannot be pgtype.Array[sqlc.Option], causes import cycle

func (opts *Options) Scan(src any) error {
    opts, ok := src.(string)
    if !ok {
        return fmt.Errorf("unsupported scan type for Options: %T", src)
    }

    options := strings.Split(strings.Trim(opts, "{}"), ",")
    *opts = Options(pgtype.Array[string]{Elements: options, Valid: true})

    return nil
}

// sqlc.yaml
...
  overrides:
    - column: "blah.options"
      go_type: "myapp/pgx/types.Options" // <-- cannot be "sqlc.Options"

But the underlying type must be pgtype.Array[string], it cannot be pgtype.Array[Option], because:

1. sqlc cannot override a type from within the same package as the generated code
2. I cannot import the sqlc generated Option type in the defined Options type, as it causes an import cycle (pkg types importing sqlc.Option and pkg sqlc importing types.Options)

This means I lose type safety and the additional methods of the Option type generated by sqlc.

From this pgx/v5 github issue, I think I need to use the pgx/v5 SQLScanner type and calling it's RegisterDefaultPgType method, but, I'm not sure if that's accurate, or how to actually do that.

What is the correct way to have pgx recognize a postgres array of user defined enum type, without losing type safety?

Electronarcosis

Answer 1

Registering the type on pgx worked for me

    dataTypeNames := []string{
        "my_custom_type_name",
        // I think this one prefixed with "_" is used for arrays
        "_my_custom_type_name",
    }
    conn := // get *pgx.Conn
    for _, typeName := range dataTypeNames {
        dataType, _ := conn.LoadType(ctx, typeName)
        conn.TypeMap().RegisterType(dataType)
    }

with something like this you could do it without manually defining your custom types.

Mediant

my_custom_type_name[] instead of _my_custom_type_name also works, and it's a bit clearer IMO. – Airdrop

Answer 2

Using lib/pq worked for me, with one caveat: you need to implement Scanner for your type:

func (o *Option) Scan(src any) (err error) {
    // ...
}

Then simply wrap the slice with pq.Array:

type Option string

var target []Option

// ...

row.Scan(pq.Array(&target))

This relieves you from using an Options type, which I think is very annoying.

Berber