[GIS] ny character limit in st_union and array_agg

postgispostgresql

I want to make a geometry array or a new geometry which is combination of few other geometries. I have one table with id column, geometry column and start_cls column. So, I want to group my ids and geometries based on start_cls value. So, I executed following query. 

SELECT array_agg(gid), st_union(geom), start_cls FROM public.edge_table_original group by start_cls limit 20

But in most of the cases, the union of geometry is giving null. I also tried with array_agg(geom) instead of st_union(geom). But the result is same. And I observed when the number of elements in the array is small, then it is giving the value of array_agg and st_union. But if the number of elements are significantly high, then it is not giving. May be there are some character limit problem. How to overcome this problem? I have added the screen shot of data output.

I also checked ST_AsTWKB. But this function returns bytea. But I need geometry or geometry[] output. As I shall use st_makeline on this geometry or geometry[].

Screen shot of the data output of this query

Best Answer

The maximum size of any Postgres object, including the results of array_agg, ST_Collect, and other array-producing aggregates, is 1 GB.

If you hit this limit, these functions won't silently return null. They'll fail with a message like this:

ERROR: array size exceeds the maximum allowed (1073741823).

For what it's worth, this object size limit roughly corresponds to a 2D LineString with 67 million vertices. I wrote a post about this topic a while ago with a few more details.

Related Question