PostgreSQL case insensitive SELECT on array

Question

I'm having problems finding the answer here, on google or in the docs ...
I need to do a case insensitive select against an array type.

So if:

value = {"Foo","bar","bAz"} 

I need

SELECT value FROM table WHERE 'foo' = ANY(value) 

to match.

I've tried lots of combinations of lower() with no success.

ILIKE instead of = seems to work but I've always been nervous about LIKE - is that the best way?

Answer 1

One alternative not mentioned is to install the citext extension that comes with PostgreSQL 8.4+ and use an array of citext:

regress=# CREATE EXTENSION citext; regress=# SELECT 'foo' = ANY( '{"Foo","bar","bAz"}'::citext[] );  ?column?  ----------  t (1 row) 

If you want to be strictly correct about this and avoid extensions you have to do some pretty ugly subqueries because Pg doesn't have many rich array operations, in particular no functional mapping operations. Something like:

SELECT array_agg(lower(($1)[n])) FROM generate_subscripts($1,1) n; 

... where $1 is the array parameter. In your case I think you can cheat a bit because you don't care about preserving the array's order, so you can do something like:

SELECT 'foo' IN (SELECT lower(x) FROM unnest('{"Foo","bar","bAz"}'::text[]) x);