3 ms·
> 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
by em500 5y ago
> 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.
- mumblemumble 5y agoI suppose I should mention that this was 15 years ago, so the behavior very well could have changed since then. At the time, though, this was happening even though I had set the columns' type affinity to TEXT. That was the very first thing I checked when the bug rolled in.
- professoretc 5y agoJust for curiosity's sake, I built SQLite 3.2.7 (from 2005-09-24) and 2.8.17 (bugfix on the old 2.8 line, from 2005-12-19) and tried this in the shell in both: sqlite> create table test_tbl ( test TEXT ); sqlite> insert into test_tbl values ( "0123" ); sqlite> select * from test_tbl; Both give the same output: 0123. Were you using an API wrapper around SQLite that was doing its own coercions, perhaps?
- em500 5y agoThanks for taking the trouble. Small syntax nitpick: in ANSI SQL, double quotes are used for identifiers (such as table or column names), single quotes for string literals ('01234'). Most databases (including sqlite) are a bit more permissive, but it's better to stick to the rules (https://www.sqlite.org/lang_keywords.html https://www.sqlite.org/lang_keywords.html)
- CRConrad 5y agoCould it even be that the double quotes around values are interpreted as some kind of "strong quotes", where single quotes wouldn't? So, inserting '0123' gets coerced to integer 123, whereas "0123" in doubles is interpreted as "No really, I mean it, this is a string!" and therefore keeps the leading zero? Just a WAG.
- professoretc 5y agoJust tried it with single-quotes and the output is the same.