PostgreSQL case insensitive SELECT on array

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);

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.

Another alternative would be with unnest()

WITH tbl AS (SELECT 1 AS id, '{"Foo","bar","bAz"}'::text[] AS value)

SELECT value
FROM  (SELECT id, value, unnest(value) AS val FROM tbl) x
WHERE  lower(val) = 'foo'
GROUP  BY id, value;

I added an id column to get exactly identical results - i.e. duplicate value if there are duplicates in the base table. Depending on your circumstances, you can probably omit the id from the query to collapse duplicates in the results or if there are no dupes to begin with. Also demonstrating a syntax alternative:

SELECT value
FROM  (SELECT value, lower(unnest(value)) AS val FROM tbl) x
WHERE  val = 'foo'
GROUP  BY value;

If array elements are unique within arrays in lower case, you don't even need the GROUP BY, since every value can only match once.

SELECT value
FROM  (SELECT value, lower(unnest(value)) AS val FROM tbl) x
WHERE  val = 'foo';

'foo' must be lower case, obviously.
Should be fast.

If you want that fast wit a big table, I would create a functional GIN index, though.