psql exports boolean columns as t or f, which SQLite will accept but treat differently from 1 and 0.
I made this unfortunate discovery when I ran the full test suite for CityQuiz for the first time in a while, and found that some of the tests failed because some cities were not listed as national capitals as expected.
I soon realized that no cities in the database were marked as capitals. The data was correct on the production site, so I immediately suspected there was a bug in the script I use to copy data from Postgres in prod to SQLite in development.
Sure enough, I found that psql serializes boolean columns as t or f, and while SQLite will accept these values, they won't work with code that expects boolean columns to be 1 or 0.
This is not the first time that SQLite's loose typing has bitten me, though, for what it's worth, SQLite's strict tables feature would have caught this issue, but it is not turned on by default
My stop-gap solution was to add UPDATE statements in the import script to convert the boolean columns to the correct values – though this requires me to remember to update the import script every time I add a new boolean column.
I'd love to know if there is a better solution to this particular problem, or a better way of accomplishing Postgres-to-SQLite export in general.
One other disturbing revelation from this debugging session is that SQLite will happily execute .import my-data.csv my_new_table; but it will treat the semicolon as part of the table's name. ∎