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

This seems hackish to me but I think it should work

SELECT value FROM table WHERE 'foo' = ANY(lower(value::text)::text[])

ilike could have issues if your arrays can have _ or %

Note that what you are doing is converting the text array to a single text string, converting it to lower case, and then back to an array. This should be safe. If this is not sufficient you could use various combinations of string_to_array and array_to_string, but I think the standard textual representations should be safer.

Update building on subquery solution below, one option would be a simple function:

CREATE OR REPLACE FUNCTION lower(text[]) RETURNS text[] LANGUAGE SQL IMMUTABLE AS
$$
SELECT array_agg(lower(value)) FROM unnest($1) value;
$$;

Then you could do:

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

This might actually be the best approach. You could also create GIN indexes on the output of the function if you want.