SQLite - (Data Type|Affinities)

Sqlite Banner


In SQLite:

  • data types are per-value, not per-column.
  • Columns have “affinities”. The type affinity of a column is the recommended type for data stored in that column. The type is recommended, not required.

Any column can store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another.

Inserted Integers are stored as integers regardless of the column type.

For instance:

  • a text column that gets passed a numeric string should store it as text, preserving the formatting (decimal places, etc).
  • a numeric column that is passed a numeric string will convert it to a number before storing it, so “10.00” would be later returned as just “10”.


create table test("col1" DOUBLE PRECISION);
PRAGMA table_info(test)
insert into test values (2);
insert into test values ("2");
insert into test values ("t");
select typeof(col1) from test3;
select sum(col1) from test3;

(Data Type|Affinities)

  • TEXT
  • REAL
  • BLOB

Date is not an affinity. It can be stored as real, integer or text.

Documentation / Reference

Discover More
Sqlite Banner

sqlite can query the json format with the JSON extension since the version 3.9.0 (2015-10-14) If you have a json string stored in a text data type, you can extract...
Sqlite Banner
SQLite - Date

The date data type in SQLite context does not exist. It is not an affinity. SQLite are capable of storing dates and times with the following data type (affinity) as: TEXT with ISO8601 strings (“YYYY-MM-DD...

Share this page:
Follow us:
Task Runner