blog

Home / DeveloperSection / Blogs / Datatypes in SQLite

Datatypes in SQLite

Prateek sharma1678 25-Jan-2018

Datatypes are an important aspect of any table created in the database. While specifying the correct datatype we can track each entry properly and avoids confusion such as deciding which value is string or integer.

SQLite supports wide verities of datatypes. But before going to datatypes directly we need to know about the SQLite storage classes.

  1. NULL – the value here is a null value.
  2. INTEGER – the value can be a signed integer which is stored in form 1, 2, 3, 4, 5, 6 or 8 bytes which totally depends upon the magnitude of the value to be stored.
  3. REAL – REAL type value is the floating type value which is stored in the form of 8-byte IEEE floating point number.
  4. TEXT – A TEXT value is the string text which is stored in the database using the encoding UTF-8, UTF-16LE or UTF-16BE.
  5. BLOB – The value to the blob is stored as it was input. 

SQLite supports the type affinity which means that any column can store any type of data but preferred storage class for a column. Some of the affinity and their description are as follows –

  1. TEXT – This type of column can store all type of data using the storage class NULL, TEXT or BLOB.
  2. NUMERIC – In this type, the column can contain the values using all the available storage class. 
  3. INTEGER – Behaves the same as a column with NUMERIC affinity, but with an exception in CAST expression. 
  4. REAL – This type consists of a column with NUMERIC affinity except that it forces integer values into floating point representation. 
  5. NONE – In this, a column with affinity NONE does not prefer one storage class over another class. 

INTEGER affinity contains the following data types –

  • INT
  • INTEGER
  • TINYINT
  • SMALLINT
  • MEDIUMINT
  • BIGINT
  • UNSIGNED BIG INT
  • INT2
  • INT8 

TEXT may contain –

  • CHARACTER(20)
  • VARCHAR(255)
  • VARYING CHARACTER(255)
  • NCHAR(55)
  • NATIVE CHARACTER(70)
  • NVARCHAR(100)
  • TEXT
  • CLOB 

NONE may contain –

  • BLOB
  • no datatype specified 

REAL contains-

  • REAL
  • DOUBLE
  • DOUBLE PRECISION
  • FLOAT 

NUMERIC affinity contains the following datatype –

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

SQLite database does not have a separate class to store Boolean or date and time. Boolean values can be stored in the form of integer 0 or 1. Whereas date and time can be stored in the form of TEXT, REAL or INTEGER values.



Updated 25-Jan-2018

Leave Comment

Comments

Liked By