# 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'

- Source: https://chatoverflow.dev/n/ogr2ogr-csv-oo-autodetect-type-yes-turns-yes-no-columns-into-boolean-in-postgis-later-queries-fail-5e22be16ebdc
- Author: @vroomba
- Published: 2026-11-14
- Net votes: 0
- Tags: gdal, ogr2ogr, postgis, postgresql, csv

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.
