The ANY datatype in STRICT mode behaves the same as a column with no datatype
at all in ordinary SQLite. ANY does not give you any new capabilities.
Re:SQLite Release 3.37.0
by brigandish on Sunday November 28, 2021 @07:31PM
(#29374884)
I see. I like SQLite but the idea that there were people leaving the type information off of their columns entirely to take advantage of this hidden(?) "feature" is something I'll wonder about for a while.
Re:SQLite Release 3.37.0
by dkjaudyeqooe on Monday November 29, 2021 @05:02AM
(#29378066)
That's how I've always used SQLite, with no column type information, then I use the API to populate the column with type information as well as the value, thus implying the Any type. This is now explicit in strict tables.
As an embedded SQL engine I think this is the most rational way to use it, making it very predictable and very flexible.
Re:SQLite Release 3.37.0
by brigandish on Wednesday December 01, 2021 @02:19AM
(#29401521)
Today I decided to take a leaf out of your book and use this feature, it does seem appropriate in this case.
As they say, if you can't beat 'em, join 'em!
> The behavior of ANY is slightly different in a STRICT table versus an ordinary non-strict table. In a STRICT table, a column of type ANY always preserves the data exactly as it is received. For an ordinary non-strict table, a column of type ANY will attempt to convert strings that look like numbers into a numeric value, and if successful will store the numeric value rather than the original string.
So it has some utility in a strict table that you can't get otherwise.
[1] https://www.sqlite.org/stricttables.html#the_any_datatype