CODEWITHSUNDEEP
sqlarrayspostgresqlcasting
Stoic_Observer
Stoic_Observer

Reputation: 159

Singularly cast an array of strings to custom enum type in WHERE clause

I am trying to cast a list of strings to a list of custom enum types. The effect I am looking for can be achieved with this:

select * from table_1 where column_a in (cast('ENUM1' AS custom_enum), cast('ENUM2' AS custom_enum))

But since I am attempting this to bypass some JPA/hibernate issues I am having with annotated filters on sql enum types, I need to dynamically add the enum strings to the list. So I am trying to figure out how to cast a whole list of strings to a list of enum_types. I'm guessing something like this:

select * from table_1 where column_a in (cast({'ENUM1', 'ENUM2'} AS custom_enum[]))

But not much I have tried gives me any luck.

Upvotes: 2

Views: 697

Answers (1)

klin
klin

Reputation: 121604

Use = ANY instead of IN. The array literal should be enclosed in quotes:

select * 
from table_1 
where column_a = any (cast('{ENUM1, ENUM2}' as custom_enum[]))

or cast the column to text:

select * 
from table_1 
where column_a::text in ('ENUM1', 'ENUM2')

Upvotes: 3

Related Questions