10 ms·
> The downside of strict tables is that some data types are not available, such as Date. There are only 5 datatypes in sqlite. INTEGER, TEXT, BLOB, REAL, and N
by e2le 2mo ago
> The downside of strict tables is that some data types are not available, such as Date.
There are only 5 datatypes in sqlite. INTEGER, TEXT, BLOB, REAL, and NUMERIC.
https://sqlite.org/datatype3.html https://sqlite.org/datatype3.html
- masklinn 2mo agonumeric is not a type it’s an affinity, the underlying types are real and integer. That is why numeric is not valid on strict tables.
- ncruces 2mo agoThe downside is that you loose the place to store the metadata that a column is supposed to store a date. Which is why I prefer not to use them.
- masklinn 2mo agoA comment will do more than a nonsensical type which is not enforced: it won’t have the wrong affinity, it will spell out what the concrete type is, and there’s no limit to what it can specify. Domain types would be the best fix, but I do not think legacy tables are the second best.
- ncruces 2mo agoA comment is not something I can get programmatically through `sqlite3_column_decltype` or `sqlite3_table_column_metadata`. I can use either API to trigger bool and time handling in my Go driver. https://github.com/ncruces/go-sqlite3/blob/main/driver/driver.go https://github.com/ncruces/go-sqlite3/blob/main/driver/drive...
- petilon 2mo agoRight, and that's the problem. There should be DATE and BOOL as well, especially in strict mode.
- asa400 2mo agoYeah the parse rules for strict tables are annoying but it doesn't change the underlying semantics: sqlite> create table test ( id integer primary key, created_at text default current_timestamp, flag integer not null default 0 check (flag in (0, 1)) ) strict; sqlite> insert into test (flag) values (1); sqlite> select * from test; ╭────┬─────────────────────┬──────╮ │ id │ created_at │ flag │ ╞════╪═════════════════════╪══════╡ │ 1 │ 2026-07-12 04:03:22 │ 1 │ ╰────┴─────────────────────┴──────╯
- mort96 2mo agoI don't understand what this has to do with strict mode? Yes those types should exist, but the workaround is to use an integer column for bool and a text column for date, whether or not you're using strict mode.
- petilon 2mo agoThe point of strict mode is that the RDBMS performs data type validation when inserting/updating data. If you use a text column for storing dates, you can store any string in the column, not just valid dates. That's not strict.