Merge intersecting geometries from 2 tables into third table

Question

I am attempting to take data from 2 tables, find intersecting geometries, and insert rows into the new table representing the intersections. I have done this in the past with CartoDB using SQL but now I'm not able to do so. I'd also be open to using the Merge Dataset functionality via the UI, but that doesn't appear to be designed for what I need.

Specifically, when I run the following sql via the UI, I get an error stating "SELECT ... INTO is not allowed here".

SELECT clipped.city_name, the_geom
INTO newtable
FROM 
    (SELECT table1.city_name, table2.namelsad, (ST_Dump(ST_Intersection(table2.the_geom, table1.the_geom))).geom As the_geom
    FROM table2
    INNER JOIN table1
    ON ST_Intersects(us_county.the_geom, us_or_city.the_geom))  As clipped

Any suggestions on how to make this work?

Answer 1

Needed to invert the insert as follows:

INSERT INTO newtable
(SELECT nextval('{sequence_name, see below}'), the_geom, null, concat(clipped.city_name, ' (', clipped.namelsad,')') AS Name
FROM 
    (SELECT table1.city_name, table2.namelsad, (ST_Dump(ST_Intersection(table2.the_geom, table1.the_geom))).geom As the_geom
    FROM us_county
    INNER JOIN us_or_city
    ON ST_Intersects(us_county.the_geom, us_or_city.the_geom))  As clipped)

And to get the sequence id for the cartodb_id column, use:

pg_get_serial_sequence({tablename}, 'cartodb_id')