[GIS] Convert data type from bytea to polygon psql

geometrygeometry-data-typepostgis

I was trying to convert data type of my column from bytea to polygon,

ALTER TABLE polygon ALTER COLUMN geom type polygon using
geom::polygon;

But I got an error saying cannot cast type bytea to polygon. Is there a solution to change the data type of the entire column or I can only use casting from ST functions.

Best Answer

You are confusing the PostgreSQL polygon geometric type with the PostGIS geometry type. They are are different approaches, and don't normally mix together.

Try converting the bytea to geometry instead:

ALTER TABLE polygon
  ALTER COLUMN geom
    TYPE geometry
    USING geom::geometry;

And if you want to restrict it to just Polygon geometry types with EPSG:4326, use typmods:

ALTER TABLE polygon
  ALTER COLUMN geom
    TYPE geometry(Polygon,4326)
    USING geom::geometry(Polygon,4326);
Related Question