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

Question

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.

klin · Accepted Answer · 2019-12-23 18:03:54Z

3

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')

answered Dec 23, 2019 at 18:03

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stoic_Observer Over a year ago

That's it! Looks like hibernate filtering doesn't like it unfortunately but thank you.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related