UPDATE an array of strings to LOWER (lowercase)

How to update a column that is an array of strings to convert each of those to lowercase?

Like this Update all values of a column to lowercase , but for an array of strings.

Example of data:

id | tags
---+---------------------------------------------------------------
58 |
87 | {Pasta}
94 | {trendy,Supper,"Restaurant casual"}
...


2 Answers
2

You could cast the array to text, apply lower() on that, then cast it back to an array:

lower()

update the_table
set tags = lower(tags::text)::text;


Maybe you could create a string from the array, lower case it, and revert it to array in one go - try:

create table myTest (
id bigserial primary key,
arrayText text);

insert into myTest (arrayText) values ('{"aPPLE","GRAPE","piNEappLe","CHErry"}');
select * from myTest;
update myTest set arrayText = string_to_array(LOWER(array_to_string(arraytext,',')),',') where id = 1;
select * from myTest;`


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.