CODEWITHSUNDEEP
sqlite
guagay_wk
guagay_wk

Reputation: 28098

When should one use data type REAL versus NUMERIC in sqlite?

In sqlite, REAL and NUMERIC data types seem similar. Since NUMERIC supports Decimal(10,5), when should one use REAL instead?

https://www.tutorialspoint.com/sqlite/sqlite_data_types.htm

I am using sqlite3.

Upvotes: 31

Views: 37567

Answers (2)

surfmuggle
surfmuggle

Reputation: 5954

I have the same question and found this sqlite forum post that specifies the storage classes

  • INTEGER, a 64-bit signed integer
  • REAL, a 64-bit IEEE-754 floating point number
  • TEXT, a bag-o-bytes that conforms to C String semantics containing UTF-8 or UTF-16 encoded data
  • BLOB, a bag-o-bytes that is nothing more than a bunch of bytes
  • NULL, a value that equals nothing else, not even itself, and is none of the above

and explains the relation between sqlite and the application / programming language

There is an invisible line between your application and the SQLite3 library. You communicate across this line using the various sqlite3_bind* calls ....

And

Each of these has a number of subinterface types _int, _int64, _double, _text, _text16, _blob, _blob64, _null depending on YOUR APPLICATION datatype (long, long long, double, char*, wchar*, unsigned char*, unsigned char* or nothing, that YOUR code is sending from or receiving into.

And

SQLite3 will automatically "convert" YOUR APPLICATION datatype to the internal type required.

So my understanding it that the "database-driver" from your application / programming language will have an impact how your application datatype will be matched to the sqlite storage class

The Type affinity documentation states:

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.

To answer your question

When should one use data type REAL versus NUMERIC

is my reply that in sqlite there are only the 5 storage classes mentioned above and there is no native numeric data type. Depending on the data you want to store and the mapping between your application / the "database driver" and sqlite you have to decide if REAL is a better fit or if you can use INTEGER.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

SQLite, unlike most other RDBMS, uses a dynamic type system. This means that any of SQLite's five storage classes can be present in any column, despite that the column belongs to one type. But obviously, it is not best practice to mix types within a single column. For example, mixing numeric and character data in the same column is bad practice, as it would be in any database. The five actual storage classes and datatypes in SQLite are:

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

  • NULL. The value is a NULL value.

  • INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

  • REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

  • TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

  • BLOB. The value is a blob of data, stored exactly as it was input.

The REAL storage class is used for numeric data with a decimal component. Floats would fit into this category. Other numbers without a decimal component would be assigned to the INTEGER storage class.

SQLite introduced a concept known as type "affinities." They were introduced in order to maximize compatibility between SQLite and other databases. From the documentation, we can see that the NUMERIC affinity is associated with the following types:

  • NUMERIC
  • DECIMAL(10,5)
  • BOOLEAN
  • DATE
  • DATETIME

So, if you are intending to store exact DECIMAL(10,5) data, then a NUMERIC affinity would make sense. On the other hand, the affinity REAL is associated with floating point (not exact) decimal data. Again, you may store either type in either column, but from a compatibility point of view, you may follow the affinity rules.

Upvotes: 52

Related Questions