Determine data type of a column in SQLite

Do you really want the type of the column, or the type of the value? (SQLite is dynamically-typed, so the distinction is important.)

If you want the latter, you can use typeof(columnName).

Use:

PRAGMA table_info(table-name);

to get table info.

Taken directly from SQLite docs about datatypes for SQLite Version 3:

Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.

Column affinity: use PRAGMA table_info(table-name);. PRAGMA table_info() gives a table with columns cid, name, type, notnull, dflt_value, and pk.

Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The "pk" column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.

Datatype of value: Use typeof(column) to see how values are actually stored by SQLite.

Example adapted from section 3.4:

CREATE TABLE t1(    t  TEXT,     -- text affinity by rule 2    nu NUMERIC,  -- numeric affinity by rule 5    i  INTEGER,  -- integer affinity by rule 1    r  REAL,     -- real affinity by rule 4    no BLOB      -- no affinity by rule 3);-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.INSERT INTO t1 VALUES(500, 500, 500, 500, 500);-- BLOBs are always stored as BLOBs regardless of column affinity.INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');-- NULLs are also unaffected by affinityINSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);

Output of PRAGMA table_info(t1);:

0|t|TEXT|0||01|nu|NUMERIC|0||02|i|INTEGER|0||03|r|REAL|0||04|no|BLOB|0||0

Output of SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; (notice each value in a column has its own datatype):

text|integer|integer|real|texttext|integer|integer|real|realtext|integer|integer|real|integerblob|blob|blob|blob|blobnull|null|null|null|null