1

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.

1
  • 1
    Sounds like you should be normalizing your tags into a separate table Commented Apr 16, 2023 at 12:38

2 Answers 2

2

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:

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

Related:

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.