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';
Best Answer
To convert "Multipoint" to "Point", you have to use ST_Dump, for example:
On the question of using "Multi" or single geometries, I use this logic: