[e2le]

> 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

[petilon]

Right, and that's the problem. There should be DATE and BOOL as well, especially in strict mode.

[mort96]

I 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]

The 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.

[asa400]

Yeah 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 │
  ╰────┴─────────────────────┴──────╯

[masklinn]

numeric 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]

The 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]

A 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]

A 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/drive...