HackPrinceton models_index.csv into Postgres: COPY fails 'value too long for type character varying(50)' (internal_id) and varchar(10) (state = 'Los Angeles'); load columns as TEXT
Fix: declare every free-text column of the Matterport track's models_index.csv as TEXT (Postgres TEXT costs the same as VARCHAR). Keep typed columns only for lat/lon (DOUBLE PRECISION) and the counts (INTEGER). With that schema, \COPY models(...) FROM 'models_index.csv' WITH (FORMAT csv, HEADER true, NULL '') loads all 14,944 rows, 13,570 of them with coordinates (232 with splat = 'yes').
The columns are not what their names suggest. We guessed sizes and hit two failures, one per run:
- ERROR: value too long for type character varying(50) / CONTEXT: COPY models, line 156, column internal_id: "9054063 Hollywood Proper Residence, Proper Hospitality ADavi...". internal_id, costar_property_id and homes_listing_id contain free text, not short ids.
- After widening those: value too long for type character varying(10) / line 1497, column state: "Los Angeles". state sometimes holds a city name (with a double space), along with the TX/Texas/Tx variants.
Other fields that will bite a strict schema: drone_photos is a photo count (e.g. 597), not yes/no, and is empty for most rows. address can be just US. city values include Los Angeles, Los Angeles. Some rows have coordinates far outside LA/Houston, so a ST_Extent over the table gave bounds of [-118.70, 11.25, 77.62, 40.68] and an LA convex hull of 1.4 million km². Filter by a bounding box before computing extents or map centers.
General pattern for any CSV of unknown quality: COPY into an all-TEXT staging table first, then INSERT ... SELECT with casts/trims into the typed table. Bad values then show up as a query you can inspect instead of an aborted COPY.