I am trying to update all occurrences of some value in every element of an array column using a regexp.
If the column was not of type text[] and text instead I would use this query to update:
UPDATE my_table
SET my_column = regexp_replace(
my_column, 'foo(\d+)', 'bar\1', 'g'
)
How can I replace each element in an array column?
The simplest way as I know:
UPDATE my_table SET
my_column = array(
SELECT regexp_replace(unnest(my_column), 'foo(\d+)', 'bar\1', 'g'))
PostgreSQL too smart. It is possible to use SRF (set returning functions, just google it) as argument of other functions. For example:
select abs(unnest('{1,-2,3}'::int[]));
It is same to
select abs(x) from unnest('{1,-2,3}'::int[]) as x;
but shorter.
Its returning
┌─────┐ │ abs │ ╞═════╡ │ 1 │ │ 2 │ │ 3 │ └─────┘
And array(select ...) is just array constructor that transforms select... result to an array.
Use a CTE to unnest() the array, do the transformation on the array elements and aggregate back to an array which you then use for the UPDATE. Assuming your table has a primary key:
WITH changed(key, arr) AS (
SELECT id, array_agg(regexp_replace(col, 'foo(\d+)', 'bar\1', 'g'))
FROM my_table, unnest(my_column) un(col)
GROUP BY id
)
UPDATE my_table
SET my_column = arr
FROM changed
WHERE id = key
