I need to lowercase array content in PostgreSQL, lower(array['array_content'])) or array[lower('array_content'])) does not work. Actual array is much, much longer.
SELECT * FROM kliendi_aadress WHERE lower(linn) LIKE ANY (array['Tallinn', 'Tartu','Narva'])
Can this even be done?
the first that come to mind is ugly:
db=# select lower(array['Tallinn', 'Tartu','Narva']::text)::text[];
lower
-----------------------
{tallinn,tartu,narva}
(1 row)
Here I lower text representation of your array and then cast it back to array.
And so comparison:
db=# select 'tartu' = any (lower(array['Tallinn', 'Tartu','Narva']::text)::text[]);
?column?
----------
t
(1 row)
Well ILIKE solved this problem for me
SELECT * FROM kliendi_aadress WHERE linn ILIKE ANY (array['Tallinn', 'Tartu','Narva']);
The citext extension can come in handy in this situation.
-- If you haven't enabled citext in your DB
CREATE EXTENSION citext;
select 'tartu' = any (array['Tallinn', 'Tartu','Narva']::citext[])
should work.
