Classic ARC/INFO Commands in PostGIS: ERASE

Having just tackled Symmetrical Difference, we'll move to the murderer's row of topological operations: ERASE, INTERSECT, IDENTITY, and UNION. Esri has a nice description of the Erase function, and I actually created a similar looking layer to the example they use:

The code is relatively straightforward:

the key to this query is in line 3 where we use ST_Difference to find those areas that don't intersect one another. From there, in lines 6 and 7 we perform a join on the attributes from both tables. Once again, instead of using *, you can select whatever fields you want, making this an even more flexible tool. And finally, if necessary, you can return to the DEFINE PROJECTION post to assign a projection to a new table.

The resulting layer looks like this: