<story><title>Quirks, Caveats, and Gotchas in SQLite</title><url>https://www.sqlite.org/quirks.html</url></story><parent_chain><item><author>at_a_remove</author><text>Me!&lt;p&gt;I&amp;#x27;m often exploring data where either there&amp;#x27;s no defined standard or the use of the data has drifted from the standard.  Now, I could go over this line-by-line, but instead my go-to has been &amp;quot;Hey, let&amp;#x27;s throw this into SQLite, then run stats on it!&amp;quot;  See what shakes out.  SQLite kindly and obediently takes all of this mystery data, which ends up being nothing like what I was told, and just accepting it.  Then I can begin prodding it and see what is actually going on.&lt;p&gt;This is something that has come up for me for at least a decade: chuck it in SQLite, then figure out what the real standard is.</text></item><item><author>ajkjk</author><text>&amp;gt; The key point is that SQLite is very forgiving of the type of data that you put into the database. For example, if a column has a datatype of &amp;quot;INTEGER&amp;quot; and the application inserts a text string into that column, SQLite will first try to convert the text string into an integer, just like every other SQL database engine. Thus, if one inserts &amp;#x27;1234&amp;#x27; into an INTEGER column, that value is converted into an integer 1234 and stored. But, if you insert a non-numeric string like &amp;#x27;wxyz&amp;#x27; into an INTEGER column, unlike other SQL databases, SQLite does not throw an error. Instead, SQLite stores the actual string value in the column.&lt;p&gt;wtf. who would ever want that?</text></item></parent_chain><comment><author>thfuran</author><text>It&amp;#x27;s reasonable to have an arbitrary&amp;#x2F;untyped data type but an integer data type should not accept non-integers.</text></comment>
<story><title>Quirks, Caveats, and Gotchas in SQLite</title><url>https://www.sqlite.org/quirks.html</url></story><parent_chain><item><author>at_a_remove</author><text>Me!&lt;p&gt;I&amp;#x27;m often exploring data where either there&amp;#x27;s no defined standard or the use of the data has drifted from the standard.  Now, I could go over this line-by-line, but instead my go-to has been &amp;quot;Hey, let&amp;#x27;s throw this into SQLite, then run stats on it!&amp;quot;  See what shakes out.  SQLite kindly and obediently takes all of this mystery data, which ends up being nothing like what I was told, and just accepting it.  Then I can begin prodding it and see what is actually going on.&lt;p&gt;This is something that has come up for me for at least a decade: chuck it in SQLite, then figure out what the real standard is.</text></item><item><author>ajkjk</author><text>&amp;gt; The key point is that SQLite is very forgiving of the type of data that you put into the database. For example, if a column has a datatype of &amp;quot;INTEGER&amp;quot; and the application inserts a text string into that column, SQLite will first try to convert the text string into an integer, just like every other SQL database engine. Thus, if one inserts &amp;#x27;1234&amp;#x27; into an INTEGER column, that value is converted into an integer 1234 and stored. But, if you insert a non-numeric string like &amp;#x27;wxyz&amp;#x27; into an INTEGER column, unlike other SQL databases, SQLite does not throw an error. Instead, SQLite stores the actual string value in the column.&lt;p&gt;wtf. who would ever want that?</text></item></parent_chain><comment><author>muttled</author><text>It&amp;#x27;s especially great for large important legacy datasets that similarly did no enforcement. You can then interact with the data and clean it up through some commands without trying to work with massive datasets in Excel where it sometimes just crashes the entire app.</text></comment>