How to filter results of a query I am using Postgis 2.4 with posgresql 10 in Windows 10.
I have the following SQL query:

WITH data (the_geom) as (
  SELECT a.gid,
          CASE WHEN ST_Within(a.geom,b.geom) THEN a.geom 
               ELSE ST_Intersection(a.geom,b.geom) 
          END AS the_geom  
  FROM source.g100_wby_lakes_r as a 
      JOIN extents.map_areas as b ON ST_Intersects(a.geom,b.geom) 
  WHERE b.map_id='AA01'
) 
INSERT into public.g100_wby_lakes_r (gid,geom) 
select * 
from data 
where st_GeometryType(the_geom)='MULTIPOLYGON';


It tries to select the intersection between two multipolygons and only insert into a new table the intersected geometries which are also multipolygons (the intersection could also result in points or lines). All works except for the WHERE clause which gives the following error:

ERROR:  column reference "the_geom" is ambiguous
LINE 4: ...id,geom) select * from data where st_GeometryType(the_geom)=...
                                                             ^
SQL state: 42702
Character: 351


Why is the_geom ambiguous?
How can I change the SQL to make it work?
Thank you
Your CTE:

(
  SELECT a.gid,
         ... AS the_geom  
  FROM ...
) 


produces a table with 2 columns, named gid and the_geom. But the "wrap" has an obvious glitch, it only has one column:

WITH data (the_geom) AS


So it says that the table will be named data and the first column (re)named to the_geom. Now you have 2 columns named the_geom! You could have the same effect - but more clear to spot - if you had written:

WITH data (the_geom, the_geom) AS


The solution is simple. Either remove the column list from that part:

WITH data AS
  ( ...
  )


or (re)name both columns explicitly:

WITH data (gid, the_geom) AS           -- explicit
(
  SELECT a.gid,
         ... AS the_geom  
  FROM ...
) 
INSERT INTO public.g100_wby_lakes_r (gid,geom) 
SELECT gid, the_geom 
FROM data 
WHERE st_GeometryType(the_geom) = 'MULTIPOLYGON' ;