unable to encode []model.Field into text format for unknown type (OID 16385): cannot find encode plan

[veqryn]

Version

1.27.0

What happened?

I am trying to create a query that will return a set of results based on a dynamic filter. I am using the pattern described here: https://github.com/sqlc-dev/sqlc/discussions/364#discussioncomment-55082 However, the query fails due to my ENUM.

I want the query to return any results where the color is any of the filtered colors. If I change the query to fav_color = @fav_colors::COLORS, then the query works. But it doesn't seem to like it when using the ANY(@fav_colors::COLORS[]), even though that pattern does work with the regular text/varchar field.

The error message: &fmt.wrapError{msg:"failed to encode args[5]: unable to encode []model.Colors{\"red\", \"blue\", \"green\"} into text format for unknown type (OID 16385): cannot find encode plan", err:(*fmt.wrapError)(0x14000092620)}

Example calling code:

	accounts, err = dao.SelectAllAccountsByFilter(ctx, model.SelectAllAccountsByFilterParams{
		AnyNames:    true,
		Names:       []string{"Jane", "John"},
		IsActive:    true,
		Active:      true,
		AnyFavColor: true,
		FavColors:   []model.Colors{model.ColorsRed, model.ColorsBlue, model.ColorsGreen},
	})
	if err != nil {
		fmt.Printf("%#+v\n", err)
	}

Relevant log output

No response

Database schema

CREATE TYPE COLORS AS ENUM('red', 'green', 'blue');

CREATE TABLE accounts (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(50)              NOT NULL,
    email       VARCHAR(50) UNIQUE       NOT NULL,
    active      BOOLEAN                  NOT NULL,
    fav_color   COLORS,
    fav_numbers INTEGER[],
    properties  JSONB,
    created_at  TIMESTAMP WITH TIME ZONE NOT NULL
);

SQL queries

-- name: SelectAllAccountsByFilter :many
SELECT *
FROM accounts
WHERE (CASE WHEN @any_names::bool THEN name = ANY(@names::text[]) ELSE TRUE END)
  AND (CASE WHEN @is_active::bool THEN active = @active ELSE TRUE END)
  AND (CASE WHEN @any_fav_color::bool THEN fav_color = ANY(@fav_colors::COLORS[]) ELSE TRUE END)
;

Configuration

version: "2"
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "../data/01_schema.sql"
    database:
      uri: "postgresql://postgres:password@localhost:5432/learning"
    rules:
      - sqlc/db-prepare
    gen:
      go:
        package: "model"
        out: "internal/model"
        sql_package: "pgx/v5"
        emit_json_tags: true

Playground URL

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

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[GiteshDalal]

I have the same issue: error while fetching products failed to encode args[5]: unable to encode []sqlc.CatalogProductType{"base", "bundle", "group", "variant"} into text format for unknown type (OID 37847): cannot find encode plan

It would be nice to know if there is any workaround. It's blocking my development. :(

[GiteshDalal]

@veqryn I found a work around for now, that is to force cast the types as text i.e. ::text

-- name: SelectAllAccountsByFilter :many
SELECT *
FROM accounts
WHERE (CASE WHEN @any_names::bool THEN name = ANY(@names::text[]) ELSE TRUE END)
  AND (CASE WHEN @is_active::bool THEN active = @active ELSE TRUE END)
  AND (CASE WHEN @any_fav_color::bool THEN fav_color::text = ANY(@fav_colors::text[]) ELSE TRUE END)
;

[varunbpatil]

See https://github.com/jackc/pgx/issues/1549 for the solution. You need to register your types with pgx.