PostGIS adding point to an existing multipoint

Question

I use PostGIS and I would like to add a new point to an existing MultiPoint using ST_Collect. The problem is that I got this error message:

ERROR: Geometry type (GeometryCollection) does not match column type (MultiPoint) CONTEXT: PL/pgSQL function inline_code_block line 10 at assignment SQL state: 22023

What do you think? What is wrong in my code? The first ST_Collect runs successfully, but the second one is not.

My code:

DO $$ 
<<first_block>>
DECLARE
  geom Geometry (MultiPoint,0);
  astext varchar;
BEGIN 
geom=ST_Collect(geom,ST_Multi(ST_GeomFromText('POINT(1 4)'))); 
geom=ST_Collect(geom,ST_Multi(ST_GeomFromText('POINT(1 10)')));
SELECT ST_AsText(geom) INTO astext;
RAISE NOTICE '%',astext;
END first_block $$;

I tried with GeometryCollection instead of MultiPoint:

DO $$ 
<<first_block>>
DECLARE
  geom Geometry (GeometryCollection,0);
  astext varchar;
BEGIN 
geom=ST_Collect(geom,ST_Multi(ST_GeomFromText('POINT(1 4)')));
geom=ST_Collect(geom,ST_Multi(ST_GeomFromText('POINT(1 10)')));
SELECT ST_AsText(geom) INTO astext;
RAISE NOTICE '%',astext;
END first_block $$;

And I've got this error:

ERROR: Geometry type (MultiPoint) does not match column type (GeometryCollection) CONTEXT: PL/pgSQL function inline_code_block line 9 at assignment SQL state: 22023

I tried with Multipoints too:

DO $$ 
<<first_block>>
DECLARE
  geom Geometry (MultiPoint,0);
  astext varchar;
BEGIN 
geom=ST_Collect(geom,(ST_GeomFromText('MULTIPOINT(1 4)')));
geom=ST_Collect(geom,(ST_GeomFromText('MULTIPOINT(1 10)')));
SELECT ST_AsText(geom) INTO astext;
RAISE NOTICE '%',astext;
END first_block $$;

Error message:

ERROR: Geometry type (GeometryCollection) does not match column type (MultiPoint) CONTEXT: PL/pgSQL function inline_code_block line 10 at assignment SQL state: 22023

Answer 1

The doc on st_collect describes this situation:

If any of the input geometries are collections (Multi* or GeometryCollection) ST_Collect returns a GeometryCollection (since that is the only type which can contain nested collections). To prevent this, use ST_Dump in a subquery to expand the input collections to their atomic elements

So to use st_collect you would dump the existing multipoints to points, include the new point and finally re-collect them all

select ST_AsText(ST_collect(dump_geom))
FROM (
    select(st_dump('MULTIPOINT(-2 3,1 2)' :: geometry)).geom as dump_geom 
    UNION
    select 'POINT(10 10)' :: geometry
    ) sub;

----------------------------
 MULTIPOINT(-2 3,10 10,1 2)

Or, as suggested by @geozelot, you can rely on st_union which will merge the points for you

 select ST_AsText(ST_Union('MULTIPOINT(-2 3,1 2)' :: geometry, 'POINT(10 10)' :: geometry));

----------------------------
 MULTIPOINT(-2 3,1 2,10 10)