st_collectionextract doesnt work as documentation states

[chrisflatley]

The documentation for st_collectionextract states:

select st_collectionextract('MULTIPOINT(1 2,3 4)'::geometry, 1);
-- POINT(1 2)

But, when I run this I just get the same data back:

D select st_collectionextract('MULTIPOINT(1 2,3 4)'::geometry, 1);
MULTIPOINT (1 2, 3 4)

Is there a function which would convert a GEOMETRY to GEOMETRY[]? The opposite of ST_Collect?

My use case for that is that I'm aggregating (via ST_Union_Agg) but it would be nice to be able to unnest to extract the individual geometries back?

[Maxxen]

Hi! Thanks for reporting this issue! This does indeed seem like an issue with the docs, I've verified against PostGIS that the behavior is correct.

I think you can use ST_Dump in combination with unnest to do what you want

SELECT UNNEST(ST_Dump('MULTIPOINT(1 2, 3 4)')).geom;
┌────────────────────────────────────────────────┐
│ (unnest(st_dump('MULTIPOINT(1 2, 3 4)'))).geom │
│                    geometry                    │
├────────────────────────────────────────────────┤
│ POINT (1 2)                                    │
│ POINT (3 4)                                    │
└────────────────────────────────────────────────┘

[chrisflatley]

That's exactly what I want. Thanks.