PerryW PerryW -4 years ago 165
SQL Question

PostgreSQL case insensitive SELECT on array

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 Source

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download