Is there a way to do an Intersect in SQL, that not only returns a geom column, but also all the attribute information?

Question

I am trying to do an intersect in SQL using the programme pgAdmin 4. I have managed to do an intersect which only returns the resulting geom column.

I would like to have all the Attribute Information from all the other columns, too though. Just like when you do an intersect in QGIS or ArcGIS.

Here is what I have used that got me only the geom column (I am using colours to make it less complicated and easier to read):

CREATE TABLE public.green AS
SELECT ST_INTERSECTION (b.geom, y.geom_2)
FROM public.blue b, public.yellow y
WHERE ST_INTERSECTS(b.geom, y.geom_2) = 't';

I know the public isn't necessary here, but I have more than one Schema that I use. So I thought it would be better to add the Schema here so all parts are accounted for.

Answer 1

you just need to change the select statement, for example this will grab everything out of both tables.

CREATE TABLE public.green AS
SELECT b.*, y.*,ST_INTERSECTION (b.geom, y.geom_2)
FROM public.blue b, public.yellow y
WHERE ST_INTERSECTS(b.geom, y.geom_2);