SQLite has a permissive type system.
The type of value is called an affinity
An affinity:
The datatype of a value is associated with the value itself, not with its container.
In a create table statement, SQLite preserves the original type name even if an affinity is applied.
Therefore, you can use any name for instance json for a text
Due to the data type affinity system, it's possible to have no data type definition on a column.
If this is the case, the data type of the column is empty and we set it to be by default a string (text).
In this demo, we show you how the data type may be empty with the table_info pragma
tabul data cat sqlite/empty_type_demo.sql@howto
drop table if exists foo;
create table foo (bar);
PRAGMA table_info('foo');
tabul data print '(sqlite/empty_type_demo.sql@howto)'@sqlite
(sqlite/empty_type_demo.sql@howto)@sqlite
cid name type notnull dflt_value pk
--- ---- ---- ------- ---------- --
0 bar 0 0
When transferring data from a third database, we don't apply the type affinity rules for type conversion letting SQLite do it.
If you want that Tabulify do it, you need to set the TYPE_AFFINITY_CONVERSION connection attribute to true.
It will then transform all external data type to affinity data type with the exception of time and boolean (why? See section below)
For instance,
Sqlite does not support natively time and boolean data types. they are seen as numeric.
Tabulify supports them with an added layer of functionalities and you can read how on this pages: