How can I update data records by trim a text array in PostgreSQL-CodePudding

I have a DB where one column is a _text type which means an ARRAY of TEXT. I need to update every row in this column to trim the white spaces for every element in that array.

I know how to do a single text in every row

UPDATE
    sites
SET
    site_id = TRIM(site_id);

but I don't know how to do it for an ARRAY of TEXT

I found a way to SELECT but I need to UPDATE

SELECT
    array_agg(trim(e))
FROM (
    SELECT
        row_number() OVER () AS rn,
        unnest(attached_sites) e
    FROM
        institutional_review_boards) t
GROUP BY
    rn;

No clue how to do it for the _text type in PostgreSQL

CodePudding user response：

You can go on with the current query except for using row_number() function such as

UPDATE institutional_review_boards AS i0
   SET attached_sites = (SELECT ARRAY_AGG(TRIM(e))
                           FROM (SELECT UNNEST(attached_sites) e
                                   FROM institutional_review_boards AS i1
                                  WHERE i1.site_id = i0.site_id) AS i)