Generated columns in strict tables
(1) By anonymous on 2025-06-18 02:49:22 [source]
Link: https://www.sqlite.org/stricttables.html
The above documentation says every column definition must specify a datatype, yet I was very confused when creating a strict table with a generated column and getting an error saying "missing datatype". This is exacerbated by the page saying "generated columns work the same", and my viewpoint that the generated column did in fact have a datatype. I see now this implicit datatype is insufficient for a strict table.
Could the documentation emphasize this? Something like:
- Every column definition must specify a datatype for that column. The freedom to specify a column without a datatype is removed. This includes generated columns, which must explicitly specify a datatype.
Or like:
- Generated columns work the same. Like other columns, a datatype must be explicitly specified.
(2) By Adrian Ho (lexfiend) on 2025-06-18 04:26:32 in reply to 1 [link] [source]
I respectfully disagree, as the existing wording:
Every column definition must specify a datatype for that column. The freedom to specify a column without a datatype is removed.
already emphasises the central point--the second sentence simply restates the first from an internal implementation POV. Adding a third statement that "this also applies to generated columns" invites the question "since you felt the need to call out a certain type of column, are there any column definitions that don't fall under this restriction?", which rather weakens the universal coverage ("every...must...") of the leading statement.
(3) By anonymous on 2025-06-18 11:19:33 in reply to 2 [link] [source]
rather weakens the universal coverage
You ignored the second suggestion where this critique does not apply.
Expressions have type in SQLite, where "create table t (a as 42) strict;" does in fact specify a type for the column.
(4) By SeverKetor on 2025-06-18 11:34:19 in reply to 3 [link] [source]
Strictly speaking, no it does not. That said, why would you want a special case for generated columns with constant values? That doesn't seem likely to come up in any practical use
(5) By anonymous on 2025-06-18 16:53:32 in reply to 4 [link] [source]
why would you want a special case for generated columns with constant values
I don't. I did not request a new feature. I requested better documentation.
Strictly speaking, no it does not.
Yes it does, because SQLite has typeof(X). I would appreciate if you would provide an example where SQLite is unable to determine the type of an expression where the expression is valid for a generated column. In particular, that means the expression must follow limitation #3 as listed at https://sqlite.org/gencol.html. If I have overlooked something, which is very possible, then an example would both clarify things and help me learn SQLite.