CODEWITHSUNDEEP
postgresql
shmnff
shmnff

Reputation: 739

SELECT with case sensitive ANY

I have a table with column of arrays that contains words in different cases. I can select all rows with required tag like this:

SELECT * FROM table WHERE 'tag' = ANY(tags::TEXT[]);

but how to take into account that words in arrays can also be in upper case?

UPDATE

that's how can I get all unique values from column of arrays:

 SELECT DISTINCT LOWER(unnest)
        FROM (SELECT unnest(tags) FROM table) AS all_tags;

maybe it will be helpful

Upvotes: 0

Views: 294

Answers (3)

shmnff
shmnff

Reputation: 739

this query finds all

SELECT * FROM (SELECT *, lower(unnest(tags)) AS val FROM table) x WHERE val = 'c++';

Upvotes: 0

klin
klin

Reputation: 121889

select *
from my_table
where 'tag' ilike any(tags)

Per the documentation:

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale.

Upvotes: 2

Hervé Piedvache
Hervé Piedvache

Reputation: 798

You need to make a choice and choose to set tag field to be upper case or not, and the content of your array also in the same state. For this you can save the tag, or update your actual content to upper or lower case and define an index on this field and then set the array content to the same state.

Upvotes: -1

Related Questions