Polygon to multi polygon using PostGIS

Question

Following https://stackoverflow.com/questions/67972764/alternative-first-function-for-geometry-type , I am trying to eliminate duplicates from a table using group by. For the aggregation method in the column geom I used the array_agg() function. It worked, but now I have the problem that this function eliminates the SRID and the geometry type (multipolygon) from the column geom.

What I tried so far is this query for giving some SRID to the geom and give back the multi polygon type. It doesen't work. If I only use the ST_Multi () outputs the same column without changing the polygon type to multi polygon, and if i try to do both at the same time like this:

...
st_srid(st_multi((array_agg(geom order by id_finca))[1]),3857) as geom 
...

outputs this error:

ERROR: the function st_srid(geometry, integer) does not exist. LINE 58: st_srid(st_multi((array_agg(array_agg(geom order by id_finca))[1]),38.... ^ HINT: No function matches in name and argument types. It may be necessary to add explicit type conversion. SQL state: 42883 Character: 1478

Any ideas how can I do it?

Answer 1

To fix the error, use the function st_setSrid instead of st_srid (the former sets the projection, while the later informs about the currently set projection).

Once you have done your type/projection, you can cast the output to the specific geometry type/projection.

st_setsrid(st_multi((array_agg(geom order by id_finca))[1]),3857)::geometry(MultiPolygon,3857) as geom