ST_MemUnion() will run a naive and slow memory friendly process. You can try that, if your problem is small enough, it might finish in a reasonable amount of time. You can also just break your problem into halves, then run the halves together. Since the resultants will have a lot fewer points than the inputs you might be able to fit the whole problem into memory that way. Or use the fast memory hungry routine on the halves and the slower routine on the final merge.
Your problem is likely because of what you have included in the group by field.
ST_UNION is an aggregate function, meaning that it is dissolving based on what you specify as the GROUP parameter.
Here is what you entered:
SELECT c.fid, ST_Union(c.boundaryshape) FROM c Group by c.fid,c.boundaryshape;
According to this, you are grouping by your fid, which is probably a unique identifier, and also by boundaryshape, which is the geometry. The problem with this is that it is highly unlikely that the features sharing a unique ID, are going to have identical geometries. Therefore, the ST_Union is probably going to end up with something like the following:
Input:
fid boundaryshape
1 11
1 12
1 11
1 13
2 12
2 13
2 12
3 5
3 2
3 4
Output:
fid boundaryshape
1 (11 + 11)
1 12
1 13
2 (12 + 12)
2 13
3 2
3 4
3 5
In this case, you should only be dissolving based on your unique identifier, unless you actually have a number of duplicate geometries with the same fid, that you are trying to get rid of. Even in that situation though, you should only need to group based on the fid, and the union will take care of the geometry duplication.
I would try the following:
SELECT c.fid, ST_Union(c.boundaryshape) FROM c GROUP BY c.fid;
That should give you the desired result of dissolving all features based on the common attribute fid.
Here is the help document at the PostGIS site: ST_Union
See the first example listed at the bottom and notice that it does not include the geometry in the group by section.
Best Answer
I should use GUnion instead of ST_Union, I tried it and it worked.
below is a list of all spatialite functions where it shows the ST & GEOS functions:
https://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html