RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/sql/sqlite-data-types/ below:

SQLite Data Types - GeeksforGeeks

SQLite Data Types

Last Updated : 23 Aug, 2024

In SQLite, understanding data types is important for efficient database design and query execution. SQLite provides five primary data types such as NULL, INTEGER, REAL, TEXT, and BLOB each of them is used for distinct purposes. In this article, We will learn about SQLite Data Types with the help of examples and so on.

SQLite Data Types

NULL- It is a NULL value.
INTEGER- It is an integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the value.
REAL- It is a floating point value, stored as an 8-byte floating number.
TEXT- It is a string, stored using the database encoding (UTF).
BLOB- It is a group of data, stored exactly as it was entered.

Type Affinity

The considered storage class for any column is called its affinity. Every table's column in the SQLite database is assigned one of the following type affinities -

TEXT - This column captures all NULL, TEXT, or BLOB data.
NUMERIC - This column captures values for all storage classes.
INTEGER - It has an exception in a CAST expression and behaves in similar way as a column with NUMERIC affinity.
REAL - It forces integer values into floating representation. and behaves like a column with NUMERIC affinity.
NONE - A column having NONE affinity do not choose one storage class above other and do not change data from one storage class to other.

Note: SQLite do not have any different storage class for storing dates and/or times. On the other hand, the TEXT, INT, or REAL could be used to store date and time values.

Determination Of Affinity

The following rules within the order shown used to declared the kind of the column and the affinity of any column:

If the declared column type has the string "INT" then it's allotted integer affinity.
If the declared column type has any of the strings like "TEXT", "VARCHAR" or "CLOB" then that column has TEXT affinity.
If the declared column type has the string "BLOB" or if no kind is given then the column has BLOB affinity.
If the declared column type has strings like "FLOA" or "DOUB" then the column has REAL affinity.
Otherwise, the affinity is NUMERIC.

Below table shows common datatype from SQL are converted into affinities by the 5 rules of the Determination Of Affinity for a small set of the datatype that SQLite can accept:

SQLite data type Type affinity INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8 INTEGER NUMERIC DECIMAL(10, 5) BOOLEAN DATE DATETIME NUMERIC REAL DOUBLE DOUBLE PRECISION FLOAT REAL CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB TEXT BLOB no datatype specified NONE

SQLite provides the typeof() function that could be used to check the storage class of a value based on its format.

Examples of SQLite Data Types

Let's look at some example of different data types in SQLite

Example 1. Using typeof function to check data type in SQLite

In this example, we use the typeof function on different values to check their data type.

Query:

SELECT typeof(200), typeof(20.0), 
typeof('200'), typeof(x'2000'), typeof(NULL);

Output:

typeof(200) typeof(20.0) typeof('200') typeof(x'2000') typeof(NULL) integer real text blob null

In SQLite, there is no need to declare a specific data type for a column while creating a table.

Example 2. Check data type of values in a table

Let us create a new table named geek_test and insert values -

CREATE TABLE geek_test (Item);

INSERT INTO geek_test (Item)
VALUES (1), (2), (10.1), (20.5), ('A'), ('B'), 
(NULL), (x'0010'), (x'0011');

Use the typeof() function to check the data type of each value stored in Item column.

SELECT Item, typeof(Item)
FROM geek_test;

Output:

Item typeof(Item) 1 integer 2 integer 10.1 real 20.5 real 'A' text 'B' text NULL null x'0010' blob x'0011' blob SQLite Data Types and Type Affinities SQLite Data Type Type Affinity INT INTEGER INTEGER INTEGER TINYINT INTEGER SMALLINT INTEGER MEDIUMINT INTEGER BIGINT INTEGER UNSIGNED BIG INT INTEGER INT2 INTEGER INT8 INTEGER NUMERIC NUMERIC DECIMAL(10, 5) NUMERIC BOOLEAN NUMERIC DATE NUMERIC DATETIME NUMERIC REAL REAL DOUBLE REAL DOUBLE PRECISION REAL FLOAT REAL CHARACTER(20) TEXT VARCHAR(255) TEXT VARYING CHARACTER(255) TEXT NCHAR(55) TEXT NATIVE CHARACTER(70) TEXT NVARCHAR(100) TEXT TEXT TEXT CLOB TEXT BLOB NONE no datatype specified NONE Conclusion

SQLite’s type affinity system offers flexibility in data storage, making it adaptable to various use cases. By understanding how type affinities are determined and how different type names map to SQLite’s affinities, developers can design more effective database schemas and perform efficient data handling.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4