ogr2ogr CSV -oo AUTODETECT_TYPE=YES turns yes/no columns into BOOLEAN in PostGIS; later queries fail 'UNION types text and boolean cannot be matched' or WHERE splat = 'yes'
Fix: drop -oo AUTODETECT_TYPE=YES (every CSV column then loads as text/varchar and you cast in SQL), or keep it and pin the types you care about with a .csvt sidecar file next to the CSV. Otherwise, update your SQL to use booleans (WHERE splat, not WHERE splat = 'yes').
What happens: GDAL's CSV driver with AUTODETECT_TYPE recognises columns whose values are only yes/no (also true/false) as Integer(Boolean). The PostgreSQL driver then creates them as boolean. ogrinfo -so -oo AUTODETECT_TYPE=YES models_index.csv models_index on the HackPrinceton Matterport models_index.csv (GDAL 3.2.2) shows:
splat: Integer(Boolean) (1.0)
defurnished: Integer(Boolean) (1.0)
ai_summary: Integer(Boolean) (1.0)The same file loaded with psql COPY into TEXT columns keeps 'yes'/'no'. So comparing two loads (SELECT splat ... FROM models UNION ALL SELECT splat ... FROM models_ogr) fails with ERROR: UNION types text and boolean cannot be matched, and code written against 'yes' stops matching. Counts are preserved (splat: 232 true / 14,712 false).
.csvt example (one quoted type per column, in column order): "String","String","String","Real","Real",...,"String". Use String for flags and for ID/ZIP columns. -oo COLUMN_TYPES=... isn't available in GDAL 3.2 ('driver CSV does not support open option COLUMN_TYPES').
DuckDB's read_csv auto-detection does the same yes/no to BOOLEAN conversion.
Verify: ogrinfo -so -oo AUTODETECT_TYPE=YES file.csv layer before loading, or \d table afterwards.