This lecture provides a detailed look at data types in SQLite.When we created our first table, we assigned a data type to each column, like INTEGER and TEXT. Choosing the correct data type is important for storing your data correctly and efficiently.
Let’s explore the main data types that SQLite uses. SQLite is a bit unique because it is “dynamically typed,” meaning you can often store any type of data in any column. However, it’s a very good practice to use the correct types for clarity and compatibility with other database systems.
The Main Data Types (Type Affinity)
SQLite has a concept called “Type Affinity,” which is the recommended type of data to be stored in a column. Here are the five affinities:
| Affinity | Description | Examples |
|---|---|---|
TEXT | A text string. | ‘Hello, world!’, ‘555-1234’, ‘2025-07-31’ |
INTEGER | A whole number (positive or negative). | 10, 0, -50, 987654321 |
REAL | A floating-point (decimal) number. | 3.14, -0.01, 123.456 |
NUMERIC | Can store any kind of number, including integers and reals. It can also handle dates and times. | 100, 12.50, ‘2024-12-25 10:30:00’ |
BLOB | Stands for Binary Large Object. Used to store raw data exactly as it is (e.g., an image or a file). | (We won’t be using this in our beginner examples) |
Why Does It Matter?
Using the correct data type helps the database work more efficiently. For example, if you declare a column as INTEGER, the database knows it only needs to compare numbers, which is much faster than comparing long text strings.
It also helps with data integrity. If you try to insert text into a column that should be an integer, the database can warn you that something is wrong.
Date and Time
A common question is: how do you store dates and times? SQLite doesn’t have a special `DATE` or `DATETIME` type like other databases. Instead, you can store them as:
- TEXT: In a format like ‘YYYY-MM-DD HH:MM:SS’.
- INTEGER: As the number of seconds since a specific point in time (known as Unix time).
- REAL: As a Julian day number.
For simplicity and readability, we will typically use the TEXT format for dates in our examples.
In the Next Lecture…
We’re going to start our first mini-project! We will use the commands we’ve learned so far (`CREATE TABLE`, `INSERT`, `SELECT`, `WHERE`) to build a simple but practical “Digital Address Book” from scratch.
Filtering Your Data with the WHERE Clause
