On 14 Jul 2015, at 6:22pm, Hayden Livingston <halivingston at gmail.com> wrote:
> Wow, this is the killer feature no-one told me about. I can get rid of > types? How does this work under-the-hood? > > SELECT * FROM TABLE WHERE FooColumn > 50 > > And I've stored "Something" in that column in some row. What's the behavior? SQLite uses column affinities not column types. You can declare a column with a type, but store any type of value in any row of that column. Once stored you can discover what type was stored using something like SELECT typeof(foo),foo FROM myTable If you insist on sorting column foo when there are different types of value stored in it, you will get a consistent but not especially useful sort order. There is a particular restriction which is the result of SQLite trying to be useful. As an example, if you define a column as REAL and store the string '123.456' in it, SQLite will automatically convert it to a number before storing it, on the assumption that you will want '123.456' to be sorted after '12.345'. So if you intend to save many different types in a column, it may be best to define it as having an affinity of BLOB. For more information read about affinity in section 2.0 of <https://www.sqlite.org/datatype3.html> Simon.
