Using ILIKE on an ENUM in SQL

Question

On a web frontend search, I would like users to be able to type 'wed', 'wednesday', 'Wed', etc. to select the day of the week. Given the enum set for days of the week, ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'), is it possible to use an ILIKE comparison on the values (WHERE day ILIKE x)? I haven't been able to make anything work and I haven't found anything through google.

Answer 1

Try casting to text:

SELECT * FROM table_name where enum_column::text ILIKE 'Mon%';

Full example:

CREATE TYPE DAY AS ENUM ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');

CREATE TABLE test (t1 DAY);

INSERT INTO test VALUES ('Monday')

SELECT * FROM test where t1::text ILIKE 'Mon%';