Find and remove portions of polygons from one geometry column that overlap from another geometry column in PostGIS

Question

Let's say I have 2 VIEWS. Each has a geometry column with polygons that overlap, approximately like so:

I'd like to delete the portions of of the green polygons where there is any overlap with the grey ones, approximately like so:

Both VIEWS have polygons that extend out of the boundaries of the example images to the left and right, respectively.

I took a shot at this with the following query (based on a similar example from a book):

SELECT my_value, ST_Intersects(g1.geom1, g1.geom2) As they_intersect
, ST_Difference(g1.geom1, g1.geom2) As difference_polygon
FROM (SELECT 
view_green.geom As geom1, 
view_grey.geom As geom2,
view_green.my_value As my_value 
FROM view_green, view_grey) AS g1;

(keeping a value associated with each polygon as well)

The output from that is the same as the VIEW and does not work (I'm using openlayers to visualize at the VIEWS/results). So as a test, I wrote a query to return only the overlapping polygons and delete the areas that overlap:

SELECT 
view_green.my_value,
view_green.timestamp,
CASE WHEN (view_green.timestamp < view_grey.timestamp)        
     THEN ST_Difference(view_green.geom, view_grey.geom)        
END
FROM 
view_green,
view_grey
WHERE ST_Intersects(view_grey.geom, view_green.geom);

(You'll notice the .timestamp because I'm trying to delete overlap from a view that is 'older'. Eventually I'd like to have a TRIGGER similar to this solution and skip the timestamp comparison)

The result of that query does not work fully but performs the deletion correctly (with st_difference()) on only the first polygon, approximately like so:

Where am I going wrong?

Answer 1

I have had similar problems with formulating erase/difference queries. I have found that dissolving/st_union the geometry you are doing the cutting with is the best way to go.

 SELECT green.*,
       coalesce(st_difference(green.shape,
                                 (SELECT st_union(gray.shape)
                                  FROM gray
                                  WHERE st_intersects(green.shape, gray.shape))), green.shape) AS geom
FROM green;