PostgreSQL: convert data of a column as array of floats to array of decimal

Question

How can I convert a column containing array of floats (or strings) to an array of decimals in PostgreSQL?

I have tried with pgAdmin 6.6, but for the column in question it shows me no data type ("Definition" -> "Data type:" -> "Select an item" -> No options)

Any idea is much appreciated.

EDIT

I have tried:

update mytable set new_column = old_column::Decimal(14,8);

but I get the following error:

ERROR:  cannot cast type numeric[] to numeric

Answer 1

You can unnest the existing column old_column into individual values and cast those as the new type in a CTE. Then use array_agg to build the individual values into an array of the new data type. Something like: (see demo)

with newagg (id, col) as
     ( select id, unnest(old_column)::decimal(14,8)
         from mytable
     )
update mytable ma
       set new_column = (select array_agg(col) 
                           from newagg na
                          where na.id = ma.id
                        ) ;

Note: This requires a unique column (here id) to ensure the Update row matches the original source row.