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?

• Inactivated Account: what's the error?
• JWW: aggregate functions are not allowed in UPDATE or set-returning functions are not allowed in UPDATE [not exactly sure b/c error is given in german]
• Inactivated Account: if you had an RECORD_ID you could use to re-join the data, you could build a new table of just the_point_geom and the record_id, then use that table to do the update instead of the aggregate function?

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.

• JWW: Alright, got it. I now solved it by creating a new table and joining the data via id number, but probably your solution will do the job more elegant as my multipoint object indeed consists of only one point each
• geozelot: @JWW It will...,) Try it, and consider accepting, so that others get the idea, too.