SQLite Data Types

1. Importance of Data Types
2. Data Types in SQLite
3. Affinity Types in SQLite
4. Use SQLite Data Types

To begin creating complex queries through procedures and functions in SQLite, we must know the different data types to avoid any loss or incorrect data manipulation.

Importance of Data Types

Data types are directly linked to the variables needed to define specific data sets within a procedure or function. The main purpose of a data type is to tell the system what kind of data will be stored within a variable.

This is crucial as the manipulation of data is going to be based on the data stored. For example, if you are storing text in a variable, adding a certain number would increase the ASCII value of the character and return a new one.

If you stored the letter A as a character and added 1 to it, it would increment the ASCII value and return B. In the same way, if you were to store the number 65 in a character variable, A would be stored, and adding one would make the value 66, which corresponds to the letter B.

This displays the importance of data types while creating functions or even within columns of a table. If you stored a number in a text data type and intended to add numbers, you’d end up with characters and vice versa.

As a result, it is crucial to have a good understanding of the data types in SQLite and the correct way to use them.

Data Types in SQLite

There are multiple data types that you will find in SQLite. To store and manipulate it correctly, you should be familiar with each of the following:

• Blob: The data stored in a blob can be considered dynamic. It is stored as it was input with no alterations.
• Null: As the name suggests, this is meant for null values. It is best to opt for null when you want to store nothing because spaces or carriage returns all have their ASCII values and are considered independent characters by the system.
• Text: This is used for all strings that need to be saved; these can be of any length as long as it is within the maximum storage capacity devised by your version of SQLite. In most cases, strings are stored using common database encodings such as UTF-8, UTF-16BE, or UTF-16LE.
• Integer: This is used for all integer values. All positive and negative whole numbers may be stored, including zero. The number of bytes used to store data depends on how big the value is.
• Real: The integer data type only caters to whole numbers, so the real data type is used for floating point values. These are numbers, both positive and negative, that also have decimal values.

Affinity Types in SQLite

It is worthwhile to discuss affinity types while we discuss data types. The data types mentioned above may also be referred to as storage classes.

A subdivision of these is affinity types, a preferred storage class designed for a column. The main purpose is to maximize compatibility between SQLite and other databases.

Affinity types in SQLite include the following:

• Text: This stores data that holds null, text, or blob values.
• Numeric: This is great if you want an affinity type that can store all five storage classes.
• Integer: This works very similarly to Numeric, but the cast expression causes differences to arise.
• Real: This is similar to integers, except it converts regular integers into floating-point values.
• None: If you use None, no storage class will have any precedence. Therefore, there will be no attempts to convert one data type to another, and it will remain as it is.

Real vs. Numeric Data Types

Since real and numeric data types overlap quite a bit, it is best to offer some clarifications so they can be used correctly. Essentially, real is used for numeric data with a decimal component, such as floating point numbers.

Other numbers without decimals are often used with the integer. This is pretty straightforward, but the affinities make it slightly tricky to understand.

Numeric is an affinity that was introduced to be affiliated with the following types of data:

• Numeric
• Decimal (10,5)
• Boolean
• Date
• Datetime

Since real is not a component in this list, it can be concluded that all kinds of floating point numbers will not be catered to in a numeric type. As a result, if you plan to store a regular integer or a decimal (10.5), numeric will work.

For any other floating point numbers, numeric is the best option.

Do keep in mind that the purpose of affinities is that the compatibility between SQLite and other databases is improved. As a result, some affiliations to certain affinities might not be directly available in SQLite.

Use SQLite Data Types

Now that you have a detailed understanding of the different data types in SQLite, you should know how to use them.

Use SQLite Data Types in Tables

You can use data types directly in columns while you are creating tables. This can be done as follows:

CREATE TABLE table_name(a INT, b TEXT, c REAL);

This creates a table with three columns of different data types.

Note: Ensure you select the correct data types while creating the table. It is possible to change them, but this involves a complex query, or the existing table must be dropped and recreated.

Use SQLite Data Types in Functions or Procedures

Creating a variable with data types on SQLite is a bit tricky. You must first create a temporary memory map table to store values to the variables correctly.

Here is an example:

BEGIN
PRAGMA temp_store = 2;
CREATE TEMP TABLE table_name(Name TEXT, Age INTEGER);
INSERT INTO table_name (Name) VALUES ('abc');
END