Is there any character limit in st_union and array_agg?

Question

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

• Michal Zimmermann: Are you sure the unerlying column is really null? Not the visual representation of pgAdmin/whatever software you use to view your data.
• LSG: Ok, then how to check the values of this column?
• John Powell: As far as I know, there is no real limit on the size a geometry can be. You can always use a test like ST_Union(geom) IS NOT NULL or wrap ST_Union(geom) in some other function like ST_NumGeometries or ST_Area. You don't want to use array_agg with geom and, as Michal has said, pgAdmin is not a reliable way of determining whether you have data or not becuse of visual representation issues.
• Michal Zimmermann: @LSG SELECT COUNT(1) FROM your_table WHERE your_geom_column IS NULL
• Michal Zimmermann: Well... What's the reasoning behind looking at random string of alphanumeric characters that doesn't say anything about what it actually represents? :) If you want to see the actual geometries, try QGIS or any other desktop GIS software.

Answer 1

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.

• John Powell: Ha, so, this serves me right for saying no real limit. I would be interested to see how long it would take to do the intersection of two geometries at this limit.