Custom ENUM in WHERE with IN comparision block

[teror4uks]

hello, I've faced with an issue where I can't use (or don't understand how) ENUM type with IN search into WHERE clause.

For example I have a query:

SELECT SUM(price)
FROM sales
WHERE product IN ('tv', 'smart_tv')

In my code the template query looks like that

SELECT SUM(price)
FROM sales
WHERE product IN ($1)

I'm using pgx pool and QueryRow() interface to get summed price. It looks like this:

  row := d.pool.QueryRow(ctx, qs, []string{"tv", "smart_tv"})

It returns the error: failed to encode args[0]: unable to encode []string{"tv", "smart_tv"} into text format for product__enum (OID 16932): cannot find encode plan

I've tried additionally registering my custom type but it doesn't work.

	pgConfig, err := pgxpool.ParseConfig(conf.DatabaseURL)
	if err != nil {
		return err
	}
	pgConfig.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error {
		log.Debug().Msg("after connet hook called")
		tm := conn.TypeMap()
		dataType, err := conn.LoadType(ctx, "product__enum")
		if err != nil {
			return err
		}
                // it returns correct oid and EnumCodec
		log.Debug().Uint32("oid", dataType.OID).Str("name", dataType.Name).Str("codec", fmt.Sprintf("%v", dataType.Codec)).Msg("dataType")
		tm.RegisterType(dataType)
		return nil
	}

Also if I'm using the string with one value instead slice without changes in my query it works as expected

 row := d.pool.QueryRow(ctx, qs, "tv")

So it seems I forgot about some additional conversions but do not know which. Could you please help to figure this out?

[teror4uks]

well, it seems I've found some solution/workaround:

if convert values to array of strings and then to array of product__enum values and compare with ANY() function...

SELECT SUM(price)
FROM sales
WHERE product = ANY(string_to_array($1, ',')::product__enum[])