How to search an enum in list of strings by postgresql query?

Question

Consider a SQL Statement:

select * from table where status in <statuses>

Where status is an enum:

CREATE TYPE statusType AS ENUM (
  'enum1',
  'enum2';

In Java I have a list of the enums in string representation:

List<String> list = new ArrayList<>();
list.add("enum1");
list.add("enum2");

I then try to build and execute the query using SqlStatement:

handle.createQuery("select * from table where status in <statuses>")
                    .bindList("statuses", statuses)
                    .map(Mapper.instance)
                    .list());

I keep getting the following error:

org.jdbi.v3.core.statement.UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: operator does not exist: status = character varying
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

I've tried wrapping each list item with CAST(enum1 as statusType) or enum1::statusType but no luck.

Any help is appreciated.

have you tried this select * from table where status::text in <statuses> — Ryuzaki L
– Ryuzaki L, Commented Oct 25, 2019 at 0:25

Ryuzaki L · Accepted Answer · 2019-10-25 11:38:20Z

3

You can cast the enum to text

handle.createQuery("select * from table where status::text in <statuses>")
                .bindList("statuses", statuses)
                .map(Mapper.instance)
                .list());

answered Oct 25, 2019 at 11:38

Ryuzaki L

40.4k14 gold badges81 silver badges111 bronze badges

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

Collectives™ on Stack Overflow

How to search an enum in list of strings by postgresql query?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related