What is the difference between SQLite integer data types like int, integer, bigint, etc.?

Question

What is the difference between integer data types in sqlite?

INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8

Which one can store 32-bit integers and which one can store 64-bit values? Is there support for 128-bit?

I find integer data size a little confusing for now, INTEGER for example can store up to 64-bit signed integers, but values may occupy only 32 bits on disk.

Calling sqlite3_column_int on an INTEGER column will work only if the value stored is less that int32 max value, how will it behave if higher?

Answer 1

From the SQLite3 documentation:

http://www.sqlite.org/datatype3.html

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

So in MS Sql Server (for example), an "int" == "integer" == 4 bytes/32 bits.

In contrast, a SqlLite "integer" can hold whatever you put into it: from a 1-byte char to an 8-byte long long.

The above link lists all types, and gives more details about Sqlite "affinity".

The C/C++ interface you're referring to must work with strongly typed languages.

So there are two APIs: sqlite3_column_int(), max 4-byte; and sqlite3_column_int64()

http://www.sqlite.org/capi3ref.html#sqlite3_int64