Re: Dissolve keeping all attributes values (concatenation/aggregation)?

On 2025-09-30 16:08 +0200, celati Laurent wrote:
> I work with qgis and postgis. I would like to perform a "dissolve":
> https://docs.qgis.org/3.40/en/docs/user_manual/processing_algs/qgis/vectorgeometry.html#dissolve
> 
> in keeping/preserving  all attributes values ? For instance : a kind of
> aggregation/concatenation within new "output features" ?
> 
> I Saw this post :
>  https://www.aproposinfosystems.com/en/about/blog/managing-attributes-postgis-dissolve/
> 
> I have tried to reproduce the proposal with my postgis table containing
> several polygons
> 
> *SELECT min(union_v4.max_hierar) as id, array_agg(union_v4.id
> <http://union_v4.id>) as ids, union_v6.geomFROM union_v4, (SELECT
> (ST_Dump(St_multi(ST_Union(geom)))).geom as geom FROM union_v6WHERE
> st_intersects(union_v4.geom, union_v6.geom)GROUP BY union_v6.geom*
> 
> But i obtain :
> 
> *ERROR: syntax error at the end of the entryLINE 12: GROUP BY union_v6.geom
> ^ERROR: syntax error at the end of the entrySQL state: 42601Characters: 285*

Your query is missing the closing parenthesis of the union_v6 subquery
(after "FROM union_v6").  This should work:

	SELECT
	    min(union_v4.max_hierar) as id,
	    array_agg(union_v4.id) as ids,
	    union_v6.geom
	FROM union_v4,
	    (SELECT
		(ST_Dump(St_multi(ST_Union(geom)))).geom as geom
	    FROM union_v6) union_v6
	WHERE st_intersects(union_v4.geom, union_v6.geom)
	GROUP BY union_v6.geom;

-- 
Erik Wienhold