SQLite data types

Determining the data type with typeof()

The data type of a value can be determined with typeof(val).

The following simple select statement selects the type of the five values, each with one of the possible types:

.mode   column
.width  10 10 10 10 10
.header on
select
   typeof( 42         )  i,
   typeof( 99.9       )  r,
   typeof('string'    )  t,
   typeof( null       )  n,
   typeof( x'deadbeef')  b
;
--
-- i           r           t           n           b         
-- ----------  ----------  ----------  ----------  ----------
-- integer     real        text        null        blob      
--

Github repository about-sqlite, path: /datatypes/5.sql

No data type specification required for columns in a create table statement

The create table statement does not require to specify a datatype for its columns.

SQlite version 3.37.0 introduced strict tables. Such tables only allow values that adhere to the defined data types in the table.

Ordering values

When values are ordered with an order by clause, the data type determines the first level of ordering: first are nulls, then numerical values (integer and real), then text and finally blobs.

int-range.py

import sqlite3
import math
import os.path

db_file = 'int-range.db'
if os.path.isfile(db_file):
   os.remove(db_file)

db = sqlite3.connect(db_file)

cur = db.cursor()

cur.execute('create table int_range(i int, ii int)')

for i in range(0, 63): # 64 would result in «OverflowError: Python int too large to convert to SQLite INTEGER»
    cur.execute ('insert into int_range values (?, ?)', ( i, 2**i))

for i in range(0, 63):
    for r in cur.execute('select ii from int_range where i = ?', (i, )):
        print math.log(r[0], 2)

Github repository about-sqlite, path: /datatypes/int-range.py

sort.py

import sqlite3
import os.path

if os.path.isfile('datatypes.db'):
   os.remove('datatypes.db')

def sel():
   print "-----------"
   print "without_dt:"
   for r in cur.execute("select without_dt from dattyp order by without_dt"):
       print "  " + str(r[0])
   
   print "dt_num:"
   for r in cur.execute("select dt_int from dattyp order by dt_int"):
       print "  " + str(r[0])
   
   print "dt_text:"
   for r in cur.execute("select dt_text from dattyp order by dt_text"):
       print "  " + (str(r[0]))




db = sqlite3.connect('datatypes.db')

cur = db.cursor()

cur.execute('create table dattyp (without_dt, dt_int integer, dt_text text)')

cur.execute("insert into dattyp values ( 2,  2,  2)")
cur.execute("insert into dattyp values ( 9,  9,  9)")
cur.execute("insert into dattyp values (19, 19, 19)")

sel()
# without_dt:
#   2
#   9
#   19
# dt_num:
#   2
#   9
#   19
# dt_text:
#   19
#   2
#   9

cur.execute("insert into dattyp values ('28', '28', '28')")
sel()
# without_dt:
#   2
#   9
#   19
#   28
# dt_num:
#   2
#   9
#   19
#   28
# dt_text:
#   19
#   2
#   28
#   9

cur.execute("insert into dattyp values ('foo', 'bar', 'baz')")

sel()
# without_dt:
#   2
#   9
#   19
#   28
#   foo
# dt_num:
#   2
#   9
#   19
#   28
#   bar
# dt_text:
#   19
#   2
#   28
#   9
#   baz

Github repository about-sqlite, path: /datatypes/sort.py