how to query with enum array ?

[liweimeng1215]

hello! when i use query with arry of enums, i get an error: Query failed. err=failed to encode args[0]: unable to encode []main.Level{"low", "middle"} into text format for unknown type (OID 16408): cannot find encode plan
first, i create a table of author and level is enum type

create table authors
(
    id    bigserial,
    name  text                         not null,
    bio   text,
    age   integer default 18           not null,
    level level   default 'low'::level not null,
    primary key (id)
);

then, i use query like this:

type Level string

const (
	LevelLow    Level = "low"
	LevelMiddle Level = "middle"
	LevelHigh   Level = "high"
)

func main() {
	ctx := context.Background()
	conn, err := pgx.Connect(ctx, "postgres://postgres:123456@localhost:5432/postgres")
	if err != nil {
		log.Fatalf("init pgx failed. err=%v", err)
	}
	rows, err := conn.Query(ctx, `
SELECT id
FROM authors
WHERE level = ANY ($1::level[])
`, []Level{LevelLow, LevelMiddle})
	if err != nil {
		log.Fatalf("Query failed. err=%v", err)
	}
	defer rows.Close()
	var ids []int64
	for rows.Next() {
		var id int64
		if err := rows.Scan(&id); err != nil {
			log.Fatalf("scan failed. err=%v", err)
		}
		ids = append(ids, id)
	}
	if err := rows.Err(); err != nil {
		log.Fatalf("rows.Err(). err=%v", err)
	}
	fmt.Println(ids)
}

Could you tell me how can i use []Level type to query the ids?

[jackc]

The issue isn't with your Go type Level. The issue is pgx has no idea what OID 16408 is. If you register the your enum and array of enum it should work. e.g.

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

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

[liweimeng1215]

thank you, jack. it works

[viktorvoltaire]

@jackc can i ask why this is only a problem with pgx?

[jackc]

@jackc can i ask why this is only a problem with pgx?

I do not know what other drivers may be doing, but if I had to guess I would say that they just assume that Go []string matches to PostgreSQL text[] and encode it with that assumption. pgx doesn't do that. pgx uses the PostgreSQL type to determine the proper encoding. For example, if []string{"foo", "bar", "baz"} is encoded into a PostgreSQL text[] then the proper encoding is {foo,bar,baz}. But if the PostgreSQL type is a json then the proper encoding is ["foo","bar","baz"]. pgx does the right thing, but to do the right thing it has to know what the underlying PostgreSQL type is.

[viktorvoltaire]

I do not know what other drivers may be doing, but if I had to guess I would say that they just assume that Go []string matches to PostgreSQL text[] and encode it with that assumption. pgx doesn't do that. pgx uses the PostgreSQL type to determine the proper encoding. For example, if []string{"foo", "bar", "baz"} is encoded into a PostgreSQL text[] then the proper encoding is {foo,bar,baz}. But if the PostgreSQL type is a json then the proper encoding is ["foo","bar","baz"]. pgx does the right thing, but to do the right thing it has to know what the underlying PostgreSQL type is.

@jackc Thank you for the explanation!

[quinn]

if i'm using pgxpool.New instead of pgx.Connect how do I do LoadType

[quinn]

nevermind, found docs for config.AfterConnect i'm good to go 👍

[talksik]

this workaround worked for me ...rest of query...AND ($2::text[] is NULL or echo_state::text = any($2::text[]))