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

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?

  • what's the error? – DPSSpatial_BoycottingGISSE Aug 23 '19 at 14:53
  • 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] – JWW Aug 23 '19 at 14:58
  • 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? – DPSSpatial_BoycottingGISSE Aug 23 '19 at 15:06
  • Thanks, it worked – JWW Aug 23 '19 at 15:24

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
    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.

  • 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 – JWW Aug 23 '19 at 15:32
  • @JWW It will...,) Try it, and consider accepting, so that others get the idea, too. – geozelot Aug 23 '19 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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