SQLite | Data Types | Inspect the Data Type of Stored Values
This article compares how SQLite stores values in columns with and without a declared type. Stored values ultimately use one of five storage classes: NULL, INTEGER, REAL, TEXT, or BLOB.
Columns Without a Declared Type
Create a table with two columns and no declared data types. Such columns have NONE affinity, so inserted values are stored without automatic conversion.
create table test (val1, val2);
$ sqlite3 test.sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite>
sqlite> create table test (val1, val2);
sqlite>
Insert three rows.
insert into test values(null, 51);
insert into test values(3.14, 'devkuma');
insert into test values('Good Morning', 1.3e-2);
sqlite> insert into test values(null, 51);
sqlite> insert into test values(3.14, 'devkuma');
sqlite> insert into test values('Good Morning', 1.3e-2);
sqlite>
Query the stored data.
select * from test;
Change .mode and .headers to make the result easier to read.
sqlite> select * from test;
|51
3.14|devkuma
Good Morning|0.013
sqlite>
sqlite> .mode column
sqlite> .header on
sqlite>
sqlite> select * from test;
val1 val2
---------- ----------
51
3.14 devkuma
Good Morni 0.013
sqlite>
Use typeof() to display each value together with its storage class.
select val1, typeof(val1), val2, typeof(val2) from test;
sqlite> select val1, typeof(val1), val2, typeof(val2) from test;
val1 typeof(val1) val2 typeof(val2)
---------- ------------ ---------- ------------
null 51 integer
3.14 real devkuma text
Good Morni text 0.013 real
sqlite>
NULL is stored as NULL, integers as INTEGER, strings as TEXT, and floating-point numbers as REAL. The stored type follows the inserted value.
Columns with a Declared Type
Next, examine how declared column types affect stored values.
Create a table whose first column has TEXT affinity and whose second has NUMERIC affinity.
create table test2 (val1 text, val2 numeric);
sqlite> create table test2 (val1 text, val2 numeric);
sqlite>
Insert three rows.
insert into test2 values ('Summer', 'Summer');
insert into test2 values (48, 48);
insert into test2 values ('72', '72');
insert into test2 values (39.24, 39.24);
insert into test2 values ('0.17', '0.17');
insert into test2 values (null, null);
sqlite> insert into test2 values('Summer', 'Summer');
sqlite> insert into test2 values(48, 48);
sqlite> insert into test2 values('72', '72');
sqlite> insert into test2 values(39.24, 39.24);
sqlite> insert into test2 values('0.17', '0.17');
sqlite> insert into test2 values(null, null);
sqlite>
Query the values and their storage classes.
select val1, typeof(val1), val2, typeof(val2) from test2;
sqlite> select val1, typeof(val1), val2, typeof(val2) from test2;
val1 typeof(val1) val2 typeof(val2)
---------- ------------ ---------- ------------
Summer text Summer text
48 text 48 integer
72 text 72 integer
39.24 text 39.24 real
0.17 text 0.17 real
null null
sqlite>
In the TEXT column, text remains text, while integer and real values are converted to TEXT. NULL remains unchanged.
For a NUMERIC column, SQLite attempts to convert text to INTEGER or REAL; if conversion fails, it remains TEXT. NULL remains unchanged.
These examples show that declaring a column type may cause values to be converted before storage.