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

• Vince: When you promise to PostGIS that you'll only assign MultiPoint values to a column, it keeps you to that promise. Note that, while legal, assigning a SRID of zero is very much not best practice. Instead use the correct SRID for the coordinate values.
• zsoooc: I tried with MultiPoint everywhere, but the problem is the same. I added this code to my question. I agree with you regarding the SRID, but now it is irrelevant, I wanted to ask a simple question, so I simplified my code.
• Vince: ST_Collect generates a collection, which is invalid for a MultiPoint column.
• geozelot: ...but ST_Union will help you keep your promises.

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)