Data Types
Every column has a data type that decides what it can hold and how it behaves: numbers add up, text sorts alphabetically, dates can be compared. Choosing the right type prevents bad data, saves space, and avoids conversion bugs. The type names vary a little between databases, so this lesson maps the common ones.
Numbers
| Type | Holds | Notes |
|---|
SMALLINT, INTEGER, BIGINT | Whole numbers | 2, 4 and 8 bytes; INTEGER is up to about ±2.1 billion |
DECIMAL(p, s) / NUMERIC(p, s) | Exact decimals | p digits in total, s after the point — use for money |
REAL, FLOAT, DOUBLE PRECISION | Approximate decimals | Fast, wide range, small rounding errors — use for measurements |
DECIMAL(8, 2) stores values up to 999,999.99 exactly. Exceeding the precision is an error in strict databases. Choose BIGINT for ids in tables that may grow past two billion rows.
Text
| Type | Holds |
|---|
CHAR(n) | Fixed length, padded with spaces — rarely useful except for fixed codes |
VARCHAR(n) | Variable length up to n characters |
TEXT | Variable length, no declared limit (PostgreSQL, MySQL, SQLite) |
In PostgreSQL, TEXT and VARCHAR perform the same, so a limit is only a validation rule. In MySQL and SQL Server, sensible VARCHAR limits help with indexing and storage. For international text, make sure the database uses Unicode (UTF-8 in MySQL is utf8mb4; in SQL Server use NVARCHAR).
Dates and times
| Type | Holds |
|---|
DATE | A calendar date |
TIME | A time of day |
TIMESTAMP / DATETIME | Date and time |
TIMESTAMP WITH TIME ZONE | An exact instant (PostgreSQL timestamptz, SQL Server datetimeoffset) |
INTERVAL | A duration (PostgreSQL, Oracle) |
Other common types
| Type | Holds |
|---|
BOOLEAN | True/false (MySQL and SQLite store 1/0; SQL Server uses BIT) |
BLOB / BYTEA / VARBINARY | Binary data (usually better stored as files, with a path in the database) |
JSON / JSONB | JSON documents (lesson 49) |
UUID | 128-bit unique identifiers |
ENUM, arrays, geometry… | Database-specific extras |
How SQLite differs
SQL
CREATE TABLE loose (n INTEGER);
INSERT INTO loose VALUES (1), ('2'), ('three'), (4.5);
SELECT n, typeof(n) FROM loose;
Result 4 rows
| n | typeof(n) |
|---|
| 1 | integer |
| 2 | integer |
| three | text |
| 4.5 | real |
SQLite uses type affinity: a column's type is a preference. Text that looks like a number is converted ('2' became an integer), but anything else is stored as given. Most databases would reject 'three' in an integer column. SQLite's STRICT tables enforce types properly:
SQL
CREATE TABLE strict_example (n INTEGER) STRICT;
INSERT INTO strict_example VALUES ('three');
Result
Error: cannot store TEXT value in INTEGER column strict_example.n
Choosing types well
- Money:
DECIMAL(12, 2), or integer cents. Never FLOAT.
- Dates: a real date/time type, never text in local formats like
'03/05/26'.
- Codes that look numeric (phone numbers, postcodes, account numbers): text. They can have leading zeros and you never do arithmetic on them.
- Yes/no flags:
BOOLEAN with NOT NULL and a default.
- Status values: text with a
CHECK constraint, or a lookup table with a foreign key.
SQL
SELECT CAST('01234' AS INTEGER) AS postcode_as_number, '01234' AS postcode_as_text;
Result 1 row
| postcode_as_number | postcode_as_text |
|---|
| 1234 | 01234 |
Try it yourself
Create a STRICT table for event tickets and see which inserts it accepts.