SQLite - (Data Type|Affinities)

About

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

Example

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
  • NUMERIC
  • INTEGER
  • REAL
  • BLOB

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

Documentation / Reference


Powered by ComboStrap