PostGIS change from multipoint to point and add column

Question

I am trying to change the geometry of a PostGIS db from multipoint to point and using this expressions works quite well

SELECT (ST_Dump(the_geom)).geom AS the_POINT_geom
  FROM MULTIPOINT_table;

But when trying to add this as new column via

ALTER TABLE  your_table ADD COLUMN the_POINT_geom double precision;

UPDATE your_table SET the_POINT_geom = (ST_Dump(geom)).geom;

this only causes an error:

set-returning functions are not allowed in UPDATE

Is there a way to add the point geometry as new column?

Answer 1

An UPDATE is a per-row operation and cannot expand the table. ST_Dump returns a SETOF RECORD which cannot be mapped to a single row directly (you can expand a SET within a subquery or CTE and return/join a single row from there), even if your MultiPoint actually consists of only one point geometry!

If that is indeed the case, you can extract that point and UPDATE the column:

UPDATE your_table
  SET the_POINT_geom = ST_GeometryN(geom, 1)
;

or, to keep only the initial column:

ALTER TABLE your_table
  ALTER COLUMN geom TYPE GEOMETRY(POINT, <SRID>)
    USING ST_SetSRID(ST_GeometryN(geom, 1), <SRID>)
;

---

If your MultiPoint consists of more than one point geometry, and you want to expand all of them into a table, you will need to create a new one using e.g. your SELECT query.