CODEWITHSUNDEEP
python-3.xpostgresqlpsycopg2
ntf
ntf

Reputation: 189

How to LOWER elements in postgresql array in SELECT statement to do case insensitive search

Can I lower the elements in an array column like I can a varchar or text column?

SELECT * FROM a_tbl a
WHERE LOWER(%(uw)s) = ANY (a.n_lst) -- how to lower?
OR LOWER(a.x) = LOWER(%(uw)s)

I saw in the documentation that I could use citext as my datatype. I might try to alter my table if there isn't a way to use LOWER

a_tbl
-----
id SERIAL PRIMARY KEY
n_lst TEXT[]
x VARCHAR(50)

Upvotes: 0

Views: 779

Answers (2)

Abdusoli
Abdusoli

Reputation: 659

You can do like below:

select lower('Jonh') = any(lower(n_lst::text)::text[]) from a_tbl;

Upvotes: 4

ntf
ntf

Reputation: 189

How to lowercase postgresql array?

SELECT * FROM a_tbl a
WHERE LOWER(%(uw)s) ILIKE ANY (a.n_lst) -- how to lower?
OR LOWER(a.x) = LOWER(%(uw)s)

Upvotes: 0

Related Questions