Postgres Array into Geoserver

Question

I'm working with OS Highways data, which when loaded into Postgres using ogr2ogr has a few columns that are set as arrays. Geoserver does not like this, so I need to transform the arrays into a simple list of comma separated values.

For example, currently we have:

{Buses,"Goods Vehicles Exceeding 7.5T"}

What I need instead is:

Buses, Goods Vehicles Exceeding 7.5T

And of course the data type needs to be changed to normal 'character varying' instead of the array type 'character varying[]'.

I could probably split the array into separate fields, then recombine, but I was interested to see if anyone had a more elegant/efficient solution. Thanks!

Answer 1

You could just alter the table and convert the column in one step:

alter table ogrtable
  alter column arrcolumn
    type varchar
    using (array_to_string(arrcolumn, ', '));