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.

Answer 2

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

Answer 3

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.

Answer 4

my solution to exclude values using a sub select...

and groupname not ilike all (
    select unnest(array[exceptionname||'%'])
    from public.group_exceptions
    where ...
      and ...
      )

Answer 5

Regular expression may do the job for most cases

SELECT array_to_string('{"a","b","c"}'::text[],'|') ~* ANY('{"A","B","C"}');

Answer 6

I find creating a custom PostgreSQL function works best for me

CREATE OR REPLACE FUNCTION lower(text_array text[]) RETURNS text[] AS
    $BODY$
         SELECT (lower(text_array::text))::text[] 
    $BODY$
LANGUAGE SQL IMMUTABLE;