---
title: "Datatypes in SQLite"  
description: "Datatypes are an important aspect of any table created in the database. While specifying the correct datatype we can track each entry properly and avo"  
author: "Prateek sharma"  
published: 2018-01-25  
updated: 2018-01-25  
canonical: https://www.mindstick.com/blog/11669/datatypes-in-sqlite  
category: "android apps"  
tags: ["android", "sqlite"]  
reading_time: 3 minutes  

---

# Datatypes in SQLite

**Datatypes** are an important aspect of any table created in the database. While specifying the correct [datatype](https://www.mindstick.com/forum/32/stored-procedure-return-datatype) we can track each entry properly and avoids confusion such as deciding which value is string or integer.

SQLite [supports](https://www.mindstick.com/blog/12043/how-to-create-a-killer-mobile-app-that-supports-your-brand) 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](https://www.mindstick.com/forum/159364/difference-between-two-expression-results-with-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](https://www.mindstick.com/articles/85595/how-to-troubleshoot-when-your-floating-pond-fountain-is-not-working-properly) 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](https://www.mindstick.com/interview/752/how-is-encoding-handled-in-ajax) 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](https://answers.mindstick.com/qa/32107/what-are-different-storage-class-specifiers-in-c) 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](https://www.mindstick.com/articles/1824/objective-c-exception-handling) 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](https://www.mindstick.com/articles/23551/an-investigate-distinctive-seafood-restaurant-for-your-image-stamp-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](https://www.mindstick.com/articles/1554/crud-operation-in-asp-dot-net-using-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.

\

---

Original Source: https://www.mindstick.com/blog/11669/datatypes-in-sqlite

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.