How to update each value in an array with a function?

Question

I want to trim spaces from each value in a text[].

If this was just a text column I could do:

update server set tags = rtrim(ltrim(tags));

However how do I do this for an array? As otherwise that same query gives an error of:

ERROR: function rtrim(text[]) does not exist
LINE 1: update server set tags = ltrim(rtrim(tags));
 ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

I have found thearray_replace function however that requires a specific index to replace. I guess I could figure out some kind of iteration for each array value and use this function.

• Charlieface: Sounds like you should be normalizing your tags into a separate table

Answer 1

What you found is the ARRAY constructor - an SQL construct, not a function, strictly speaking. It's the right tool in combination with the set-returning function unnest().
Two noteworthy optimizations, though:

UPDATE server
SET tags = ARRAY(SELECT trim(unnest(tags)))
WHERE tags IS DISTINCT FROM ARRAY(SELECT trim(unnest(tags)));

Most importantly, the added WHERE clause filters rows that wouldn't change. Updating those would achieve nothing, at full cost. See:

• How do I (or can I) SELECT DISTINCT on multiple columns?

And just use trim() instead of rtrim(ltrim(...)).

Related:

• Apply trim() and regexp_replace() on text array
• Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Answer 2

By chance I found the array function though I don't know exactly what it does or where it's documented (it does not seem to be listed here https://www.postgresql.org/docs/12/functions-array.html).

update server
set tags = array(select rtrim(ltrim(unnest(tags))));

The unset function is used to expand an array to a set of rows. Which we then use array to turn back into an array afterwards.