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.

2 Answers 2


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:

ALTER TABLE my_table
    ALTER COLUMN geom TYPE geometry(MultiPoint,4326) USING ST_Multi(geom);

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:

SELECT COUNT(CASE WHEN ST_NumGeometries(geom) > 1 THEN 1 END) AS multi_geom,
       COUNT(geom) AS total_geom
FROM my_table;

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:

ALTER TABLE my_table
    ALTER COLUMN geom TYPE geometry(Point,4326) USING ST_GeometryN(geom, 1);

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 column geom, here are the steps to convert to multi-part:

-- 1. Remove the geom_type constraint (if existing)
ALTER TABLE my_table DROP CONSTRAINT enforce_geotype_geom;

-- 2. Update the geometry data to multi-part -- skip if it is an empty table
UPDATE my_table SET geom = ST_Multi(geom);

-- 3. Re-add a different geometry constraint for the new type
ALTER TABLE my_table ADD CONSTRAINT enforce_geotype_geom
  CHECK (geometrytype(geom) = 'MULTIPOINT'::text OR geom IS NULL);

-- 4. Update the geometry_columns metadata table
UPDATE geometry_columns SET type = 'MULTIPOINT'
WHERE f_table_schema = 'public' AND f_table_name = 'my_table' AND f_geometry_column = 'geom';
  • Hi @Mike Toews (and Ulrik). I don't think your second step for PostGIS 1.x is necessary in this case, Mike. Ulrik said the table will be empty at the time of the type conversion, so there won't be any non-multi values to cause an error with something like: 1) ALTER TABLE my_table DROP CONSTRAINT enforce_geotype_the_geom; 2) ALTER TABLE my_table ADD CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOINT'::text OR the_geom IS NULL); then 3) UPDATE geometry_columns SET type = 'MULTIPOINT' WHERE f_table_name = 'my_table'; (perhaps the sloppiest comment ever - my bad) Commented Jul 11, 2012 at 17:56
  • @rec.thegeom correct; with an empty table there would be nothing to update. Thanks for posting the actual commands!
    – Mike T
    Commented Jul 11, 2012 at 18:47
  • If you have complex data in various forms like GEOMETRYCOLLECTION (MULTIPOLYGON(...)) then you might want to change the query for detection of the more than one geometry. With check like ST_NumGeometries(ST_CollectionHomogenize(geom)) > 1 and use similar thing for the USING with: ST_GeometryN(ST_Multi(ST_CollectionHomogenize (geom)), 1) or similar.
    – Ravbaker
    Commented May 10, 2019 at 15:12

Change, I don't think so. But you can create a new table with identical structure, except for the geom column, then run:

SELECT AddGeometryColumn('new-pt_table','geom',<SRID>,'MULTIPOINT',2);

INSERT INTO new_pt_table (attr1, attr2, attr3, ..., geom) 
SELECT attr1, attr2, attr3, ... , ST_Multi(geom) FROM old_pt_table;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.