Using regexp_replace on array column in postgres

Question

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?

Answer 1

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.