CODEWITHSUNDEEP
databasesqlitesqldatatypes
Almas Dusal
Almas Dusal

Reputation: 663

Is NUMERIC and INTEGER types are exaxtly same in SQLite?

Is Numeric and Integer types are exactly same or not in SQLite Database?

I have some confusion about those types. If there are any difference then what are the differences?

Upvotes: 5

Views: 4497

Answers (1)

Shawn
Shawn

Reputation: 52409

NUMERIC is not a type (AKA storage class), it's an affinity. INTEGER is both.

From the documentation (emphasis added):

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal, respectively. If the TEXT value is a well-formed integer literal that is too large to fit in a 64-bit signed integer, it is converted to REAL. For conversions between TEXT and REAL storage classes, only the first 15 significant decimal digits of the number are preserved. If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT. For the purposes of this paragraph, hexadecimal integer literals are not considered well-formed and are stored as TEXT. (This is done for historical compatibility with versions of SQLite prior to version 3.8.6 2014-08-15 where hexadecimal integer literals were first introduced into SQLite.) No attempt is made to convert NULL or BLOB values.

A string might look like a floating-point literal with a decimal point and/or exponent notation but as long as the value can be expressed as an integer, the NUMERIC affinity will convert it into an integer. Hence, the string '3.0e+5' is stored in a column with NUMERIC affinity as the integer 300000, not as the floating point value 300000.0.

A column that uses INTEGER affinity behaves the same as a column with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity is only evident in a CAST expression.

(Reading and understanding that entire page and the concepts it describes, like affinity, is essential for learning how sqlite works.)

The rules for CAST() are too involved to quote fully, but can be read here. To summarize, casting to INTEGER will always return an integer, casting to NUMERIC can return an integer or a real value, depending.

Upvotes: 8

Related Questions