Error when creating new view using ST_Union (PostGIS)

Question

I have a table in PostGIS (2.1.7) containing adjacent multipolygons, and I'd like to create a view containing polygons which are merged versions of the multipolygons based on some field value.

I tried the following query:

CREATE VIEW results AS
SELECT somefield, ST_Union(geom)::geometry(Polygon, 2056) AS geom FROM somelayer GROUP BY somefield;

and I got the following error:

Geometry type (MultiPolygon) does not match column type (Polygon)

I thus tried to change the result type to multipolygon:

CREATE VIEW results AS 
SELECT somefield, ST_Union(geom)::geometry(MultiPolygon, 2056) AS geom FROM somelayer GROUP BY somefield;

But it didn't help since I got the following error:

Geometry type (Polygon) does not match column type (MultiPolygon)

The following query finally worked but it is not optimal since I'd like to have polygons instead of multipolygons (it should be possible since the resulting polygons are all singlepart):

CREATE VIEW results AS 
SELECT somefield, ST_Multi(ST_Union(geom))::geometry(MultiPolygon, 2056) AS geom FROM somelayer GROUP BY somefield;

How can I get the desired results?

Answer 1

CREATE VIEW results AS ( 
SELECT somefield, (ST_Dump(ST_Union(geom))).geom AS geom FROM somelayer GROUP BY somefield);

• wiltomap: I have the same problem using your suggestion @MichalZimmermann... The query runs OK but the geom column geometry is geometry, not geometry(MultiPolygon). This causes display issues in QGIS with DB Manager...