I would like to match {[email protected]} with {[email protected], [email protected]}
Doing the case sensitive query is each enough:
select * from users where email @> '{[email protected]}'
However, I cannot find a way to run a case insensitive contains query against an array column.
This page does not instill confidence that this is possible, as there is no mention of case sensitivity. Any thoughts?
A solution that works, but is going to be very slow for larger tables and array is to unnest the array and use ILIKE (or any other case insensitive comparison):
select *
from (
select id, unnest(email) email_address
from user
) t
where email_address ILIKE '[email protected]'
I think you can't do it directly, because there are no case insensitive functions for postgresql arrays. The easiest way is to keep both array data and queries in lowercase.
Here is similar problem: Creating case-insensitive indexes on Postgres string array
You can use LOWER/UPPER functions.
select * from users where LOWER("email"::text)::text[] @> '{[email protected]}'
