Postgres Array[VarChar] uppercase?

Question

I'm trying to find a way to force an array to make it upper or lowercase. This is so that no matter what the user inputs they get a result. This is the query:

select * from table where any(:id) = databasecolumn

:id is an array of chars that the user inputs(can be lowercase or uppercase) and I need to make sure that whatever the user inputs they get a result.

This works as long as the user inputs in uppercase (because the database values are also uppercase). But when they input lowercase letters they get no response.

I tried this:

select * from table where any(upper(:id)) = upper(databasecolumn)

but this does not work because the function "upper" is not for arrays. It works fine when I do it with a single input but not arrays.

Do you have any pointers? I couldn't find an equivalent function for an array of varchars.

What are you trying to do? By default, isn't the comparison already case-insensitive? Why do you need to apply upper at all? — Siyual
– Siyual, Commented Mar 21, 2017 at 12:44

user330315 · Accepted Answer · 2017-03-21 13:03:12Z

3

You could use ILIKE:

select * 
from table 
where databasecolumn ILIKE any(:id);

This:

with data (col) as (
  values ('one'), ('Two'), ('THREE')
)
select *
from data
where col ilike any(array['one', 'two', 'three']);

returns:

col  
-----
one  
Two  
THREE

answered Mar 21, 2017 at 13:03

user330315

Sign up to request clarification or add additional context in comments.

Comments

Vao Tsun · Accepted Answer · 2017-03-21 12:51:35Z

1

you can use double casting like here:

t=# with a as (select '{caSe1,cAse2}'::text[] r) select r,upper(r::text)::text[] from a where true;
       r       |     upper
---------------+---------------
 {caSe1,cAse2} | {CASE1,CASE2}
(1 row)

It neglects the benefits of using ANY though

answered Mar 21, 2017 at 12:51

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Postgres Array[VarChar] uppercase?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related