How to explicit typecast an ENUM type in a LIKE search?

Question

Today I'm facing an interesting issue with Postgres and I can't find a straight solution.

I made a custom ENUM type like:

CREATE TYPE public.type_emergency_status AS ENUM
('OPEN', 'OPEN.ATTENDED', 'OPEN.DEFERRED', 'ABANDONED', 'CLOSED.CANCELED', 'CLOSED.RESOLVED', 'CLOSED.UNRESOLVED');

In my table, I have defined a field as this type. Now, I want to find all the records with emergency_status LIKE 'CLOSED%'

If I make a simple query like:

SELECT * FROM <table_name> WHERE <field> LIKE 'CLOSED%'

And obtain:

ERROR:  operator does not exist: type_emergency_status ~~ unknown
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

The question is, where do I do the explicit cast?

I have tried several ways, but haven't found where to do thad.

Any DB expert on this?

Answer 1

Try this: SELECT * FROM <TABLE_NAME> WHERE <field>::text ILIKE 'CLOSED%';

Answer 2

To make it more safe and index friendly:

SELECT *
FROM <table_name>
WHERE <field> in (
    select enumlabel::type_emergency_status
    from pg_enum
    where enumtypid = 'type_emergency_status'::regtype and enumlabel like 'CLOSED%');