Problem with ST_Difference in PostGIS

Question

I'm testing various SQL queries to do a seemingly easy ST_Difference in PostGIS. It needs to be done in SQL as I'm testing to later use the query on quite large datasets.

Original code I tried:

CREATE TABLE test_diff (
    geom geometry(MultiPolygon, 2154)
);

CREATE INDEX idx_test_diff_geom
ON test_diff
USING GIST (geom);

INSERT INTO test_diff (geom)
SELECT ST_CollectionExtract(ST_Difference(a.geom, b.geom), 3)::geometry(MultiPolygon, 2154)
FROM test_st_diff1 AS a
JOIN test_st_diff2 AS b
ON ST_Intersects(a.geom, b.geom);

This works, but only on the polygons that intersect each other. I want those who don't intersect to also get 'cut'.

So I tried the next code to try to fix it:

CREATE TABLE test_diff_result AS
SELECT ST_Difference(a.geom, b.geom) AS geom
FROM test_st_diff1 AS a, test_st_diff2 AS b;

The problem with this one is that it doesn't affect the first dataset at all.

Answer 1

I think this should cover all possible scenarios:

1. Polygon(s) not intersected by an eraser polygon: the geometry stays unchanged
2. Polygon(s) intersecting one or multiple eraser polygons: the geometry is erased/differenced by an union of all intersecting erase polygons, so the input polygon isnt duplicated for each erase polygon

If you want to keep more fields than the id field from table a, add them to the select and group by rows.

SELECT a.id, COALESCE(ST_DIFFERENCE(a.geom, ST_UNION(b.geom)), a.geom) AS geom
FROM test.patch AS a
LEFT JOIN test.eraser AS b
ON ST_INTERSECTS(a.geom, b.geom)
GROUP BY a.id, a.geom

Answer 2

Like @BERA's generally spot-on answer but using a LATERAL join, allowing for more flexibility in the SELECT (no grouping):

SELECT
  base.id,
  ...,
  COALESCE(
    diff.geom,
    base.geom
  ) AS geom
FROM
  <base_table> AS base
  LEFT JOIN LATERAL (
    SELECT
      ST_Difference(
        base.geom,
        ST_Union(blade.geom)
      ) AS geom
    FROM
      <blade_table> AS blade
    WHERE
      ST_Intersects(base.geom, blade.geom)
  ) AS diff ON TRUE
;