Case insensitive array contains query in Postgres

Question

I would like to match {TEST@EXAMPLE.COM} with {test@example.com, other@example.com}

Doing the case sensitive query is each enough:

select * from users where email @> '{test@example.com}'

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?

score 4 · Answer 1 · answered Sep 29 '13 at 08:09

4

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 'test@example.com'

answered Sep 29 '13 at 08:09

Works great for pagination posts by tags, using LIMIT and OFFSET on the inner select the query execute in little to no time :) – Euler Ribeiro Sudbrack Aug 30 '18 at 14:42

score 2 · Answer 2 · edited May 23 '17 at 11:43

2

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

edited May 23 '17 at 11:43

Community

1
1

answered Sep 29 '13 at 07:37

Tomasz Myrta

1,114
8
10

user2071764 · Answer 3 · 2023-02-23T11:44:48.770

0

You can use LOWER/UPPER functions.

select * from users where LOWER("email"::text)::text[] @> '{test@example.com}'

edited Feb 23 '23 at 11:44

answered Feb 23 '23 at 11:44

user2071764

1
1

Case insensitive array contains query in Postgres

3 Answers3