How to change the geometry type from Point to Multipoint within an existing table in PostGIS?

Question

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.

Answer 1

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_name = 'my_table' AND f_geometry_column = 'geom';

Answer 2

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;