SQLite Data Type Support

Sqlite

About

SQLite has a permissive type system.

Properties

Affinities

The type of value is called an affinity

An affinity:

  • has 6 possible values
    • text
    • numeric
    • integer
    • real
    • blob
    • null
  • is stored at the cell level (row/column) as seen by the citation below.

The datatype of a value is associated with the value itself, not with its container.

Type declaration

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

Text default

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
  • The result, where you can see an empty value in the type column.
(sqlite/empty_type_demo.sql@howto)@sqlite
cid   name   type   notnull   dflt_value   pk
---   ----   ----   -------   ----------   --
  0    bar                0                 0

Transfer Conversion

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,

  • float would become real
  • double would become real
  • smallint would become integer
  • …

Time and Boolean Data Type

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:

Task Runner