Convert PostgreSQL Polygon to PostGIS POLYGON

Question

I have an existing table in postgres which contains a plain old posgresql polygon. I have added a new column using

SELECT AddGeometryColumn ('public','fences','geom',4326,'POLYGON',2);

I would like to populate this GIS column with the polygon data from the existing column.

The existing column is called poly and the data looks like the following:

"((-74.026142,40.636264),(-74.026142,40.636438),(-74.025818,40.636438),(-74.025818,40.636264),(-74.026142,40.636264))"

Is there a query I can run that will pull that poly field convert it to a postgis polygon and insert it into the new geom column?

Answer 1

It's not an elegant solution, but you could try to convert Polygon string representation into WKT and use ST_GeomFromText

select ST_GeomFromText(
    'POLYGON ' || 
    regexp_replace(
        '((-74.026142,40.636264),(-74.026142,40.636438),(-74.025818,40.636438),(-74.025818,40.636264),(-74.026142,40.636264))', 
        '\,(?=[0-9])', ' ', 'g') 
)