6 ms·

Hot take: The ANY column type will never be used. Users who want to take advantage of it will just avoid the STRICT keyword on the whole table (maybe even the

Hot take: The ANY column type will never be used. Users who want to take advantage of it will just avoid the STRICT keyword on the whole table (maybe even the entire database). Users who like STRICT typing will reach for a JSON column for their occasional weakly- or sum-typed needs.

brigandish 5y ago
ANY acts differently between strict and non-strict[1]: > 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 https://www.sqlite.org/stricttables.html#the_any_datatype
- SQLite 5y ago
  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.
  - brigandish 5y ago
    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.
    - dkjaudyeqooe 5y ago
      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.
      - brigandish 5y ago
        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!
pdimitar 5y ago
I'll start using it tomorrow for one reason: three STRICT tables feature set will very likely get gradually expanded with time and I believe it's also very likely that sticking to the new syntax will thus net increasing benefits with time. Of course, migrating current databases is another story. They have plenty of CHECK constraints and app-level validation already so the value in migrating those is small (although I'd still do it because the ANY data type columns will not coerce / convert anything when the table is STRICT which to me is still a win).

brigandish 5y ago

ANY acts differently between strict and non-strict[1]: > 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 https://www.sqlite.org/stricttables.html#the_any_datatype

SQLite 5y ago
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.
- brigandish 5y ago
  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.
  - dkjaudyeqooe 5y ago
    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.
    - brigandish 5y ago
      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!

pdimitar 5y ago

I'll start using it tomorrow for one reason: three STRICT tables feature set will very likely get gradually expanded with time and I believe it's also very likely that sticking to the new syntax will thus net increasing benefits with time. Of course, migrating current databases is another story. They have plenty of CHECK constraints and app-level validation already so the value in migrating those is small (although I'd still do it because the ANY data type columns will not coerce / convert anything when the table is STRICT which to me is still a win).