Is there a PostGIS function that can change geometry type for an existing table?
We need to change from POINT to MULTIPOINT.
The table will be empty when we change the geometry type and we cannot just drop/create the table.
postgis
Is there a PostGIS function that can change geometry type for an existing table?
We need to change from POINT to MULTIPOINT.
The table will be empty when we change the geometry type and we cannot just drop/create the table.
Best Answer
For PostGIS 2.x, you can use the ALTER TABLE DDL using an expression.
To convert from a single-part to multi-part geometry, use ST_Multi:
To convert from a multi-part to a single-part geometry, it is a bit more tricky since you can only use one part, and ignore all other parts (if they exist). Check your data first to see if you have some geometries with more than one part:
If you see
multi_geom
greater than 0, then you will risk loosing data, and you should probably keep it as a multi-part geometry. If you see 0, then it is safe to make into a single-part geometry with:For PostGIS 1.x, it is a bit more messy, as there are several steps (thanks @rec.thegeom!).
Assuming a table
my_table
and geometry columngeom
, here are the steps to convert to multi-part: