PostGIS query returns duplicated rows

Question

I'm trying to create a shapefile of a state. I have a map of US zipcodes that I downloaded from the US Census and I want do create a shapefile of zipcodes in Florida.

I ran this query which produces a shapefile that I expected, however when I examine the attribute table in QGIS, I see that there are duplicates of the zip code. The same thing occurred when I modify the query to intersect a specific state using a shapefile of US DMAs (Flordia has 11 DMAs, but the attribute table has 1,107 rows, mostly duplicates). Answers regarding how to fix duplicate rows in an attribute table has come up unsatisfactory. An example of my output is here:

My query is below:

"SELECT geoid10, geom FROM (
    SELECT uszips.geoid10, (ST_Dump(ST_Intersection(uszips.geom, fl.geom))).geom
    FROM uszips, fl
    WHERE ST_Intersects(uszips.geom, fl.geom)) t
WHERE ST_GeometryType(geom) = 'ST_Polygon';

Answer 1

You're getting duplicates because ST_Dump is breaking up multi geometries and/or geometry collections into constituent parts, each of which has the same ID of the original geometry. So if a MultiPolygon has 5 Polygons, you'll get 5 Polygons with the same ID. You can assign a new ID to each shape if you want, but keeping the original ID will help with joins and grouping, etc.

SELECT row_number() over() as newid, geoid10, geom FROM (
    SELECT uszips.geoid10, (ST_Dump(ST_Intersection(uszips.geom, fl.geom))).geom
    FROM uszips, fl
    WHERE ST_Intersects(uszips.geom, fl.geom)
) t
WHERE ST_GeometryType(geom) = 'ST_Polygon'

row_number is a window function that just generates a unique ID for each row.

Or, union and group by ID.

SELECT geoid10, ST_Union(geom) AS geom FROM (
    SELECT uszips.geoid10, (ST_Dump(ST_Intersection(uszips.geom, fl.geom))).geom
    FROM uszips, fl
    WHERE ST_Intersects(uszips.geom, fl.geom)
) t
WHERE ST_GeometryType(geom) = 'ST_Polygon'
GROUP BY geoidid