> That said, I am one of those people who would prefer more rigid typing. I'm cautious about using SQLite at work specifically because I've been burned by that first example it gives. I had a situation where a client needed to use all-numeric IDs where the leading zeros were significant. (For example, "1234" and "01234" were two different IDs.) It was insisting on converting the string "01234" to the integer 1234. So when I tried to get it back out, it would give me the (non-equivalent) string "1234." I had a bugger of a time dealing with this - I eventually had to implement a workaround where all of these IDs were prefixed with a "#" character simply to force SQLite to stop trying to be helpful.

I think you're misremembering some crucial details. Sqlite only attempts to convert '01234' to the integer 1234 on insertion in columns defined with numeric type affinity. If it did so succesfully, it will return the value as the integer 1234, not as a string. The only way to preserve leading zeros is to store it as a string, and sqlite will do exactly that if the column was defined with text affinity. Moreover, this is the same behavior as in other databases: in mysql if you try to insert the value '0123' in a INT column, both with and without quotes, it will silently cast to the integer 123 and return that on retrieval.

I just tested all of this on both sqlite3 and mysql, but from memory, postgresql behaves the same.