Cannot retrieve array of custom enums using pgx

[kntajus]

Given this SQL:

CREATE TYPE fruit AS ENUM ('apple', 'banana', 'kiwi');

CREATE TABLE choices (
	id int PRIMARY KEY,
	fruits fruit[] NOT NULL
);

-- name: GetChoice :one
SELECT * FROM choices WHERE id = $1;

along with this config:

version: "2"
sql:
- schema: "query.sql"
  queries: "query.sql"
  engine: "postgresql"
  gen:
    go:
      out: "db"
      sql_package: "pgx/v4"

When calling the generated GetChoice function, it panics:

panic: reflect.Value.Convert: value of type *[]db.Fruit cannot be converted to type *[]string

(Example code)

I can't work out if the problem lies with sqlc, pgx, or (more likely) me. Possibly worth noting that if I modify the generated code to make use of pq.Array when scanning into the array, it then works.

Anyone able to advise if I'm doing something wrong?

[christeredvartsen]

I'm also seeing this behaviour, have not found a workaround yet.

[christeredvartsen]

I'm also using pgx/v4 btw.

@kyleconroy Do you know of a workaround?

[toqueteos]

Issue is still going with sqlc 1.18.0 and pgx/v5. Any news on this?

https://play.sqlc.dev/p/8d5d1b443709dff245e8de035f49175a1fe9cc303e0835cef2cb6b8e70c97964

EDIT: I'm not sure if this would be solved by generating a sql.Scanner implementation for the slice of enum too not just for the enum type itself.

[ryu-ichiroh]

@kntajus @christeredvartsen @toqueteos

The issue is related to https://github.com/jackc/pgx/issues/1549#issuecomment-1467107173.

It can be resolved by running LoadType() once beforehand. https://pkg.go.dev/github.com/jackc/pgx/v5#Conn.LoadType

CREATE TYPE fruit AS ENUM ('apple', 'banana', 'kiwi');

For the issue fruit enum, add the below code.

t, err := conn.LoadType(context.Background(), "fruit") // level type
// check error
conn.TypeMap().RegisterType(t)

t, err = conn.LoadType(context.Background(), "_fruit") // array of level type
// check error
conn.TypeMap().RegisterType(t)

To use array of custom enums with pgxpool, It would be good to add the code in Config.AfterConnect().

var pgTypes = []string{
	"fruit",
	"_fruit",
}

config.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error {
	for _, typ := range pgTypes {
		t, err := conn.LoadType(ctx, typ)
		conn.TypeMap().RegisterType(t)
	}
}

db, err := pgxpool.NewWithConfig(ctx, config)

See more detail https://github.com/jackc/pgx/issues/1601

[toqueteos]

@ryu-ichiroh I sent #2510 to help with that for both pgx/v4 and pgx/v5

[parmetra]

@toqueteos

It's still an actual issue. Are there any actions to fix this problem?