Every column has a type that decides what it can hold, how much space it takes and how it compares and sorts. Choose the narrowest type that fits the data forever.

Standard SQL types

Category	Types	Use for	Notes
Integer	`SMALLINT`, `INT`, `BIGINT`	Counts, ids, quantities	2 / 4 / 8 bytes; `INT` max ≈ 2.1 billion
Exact decimal	`DECIMAL(p, s)` / `NUMERIC(p, s)`	Money, rates	`DECIMAL(10,2)` = 8 digits before the point, 2 after; no rounding errors
Approximate	`REAL`, `FLOAT`, `DOUBLE PRECISION`	Measurements, science	Binary floating point — `0.1 + 0.2 ≠ 0.3`
Fixed text	`CHAR(n)`	Codes of fixed length (`'IN'`, `'USD'`)	Padded with spaces to `n`
Variable text	`VARCHAR(n)`, `TEXT`	Names, emails, descriptions	Stores only what is used (+1–2 bytes length)
Date & time	`DATE`, `TIME`, `TIMESTAMP`, `TIMESTAMPTZ`, `INTERVAL`	Events, birthdays	Store UTC; use `TIMESTAMP WITH TIME ZONE` where available
Boolean	`BOOLEAN`	Flags	MySQL: `TINYINT(1)`; SQL Server: `BIT`
Binary	`BLOB`, `BYTEA`, `VARBINARY`	Files, hashes	Large files usually belong in object storage, not the DB
Other	`UUID`, `JSON`/`JSONB`, `ENUM`, arrays	Engine-specific	PostgreSQL is richest here

CHAR vs VARCHAR

Money: DECIMAL, never FLOAT

SQL

SELECT 0.1 + 0.2        AS float_sum,
       0.1 + 0.2 = 0.3  AS is_equal;

Output

float_sum           | is_equal
--------------------+---------
0.30000000000000004 | 0

Floating-point numbers are binary fractions, so decimal values like 0.1 are approximated. DECIMAL(p, s) stores exact base-10 digits — use it (or integer paise/cents, as the sample database does) for money.

SQLite: types belong to values

Most engines reject a value that doesn't fit the column type. SQLite uses type affinity: the column prefers a type and converts when it can, but otherwise stores what it is given. typeof() shows the stored type.

SQL

SELECT typeof(42), typeof(4.2), typeof('42'), typeof(NULL), typeof(x'CAFE');

CREATE TABLE loose (n INTEGER);
INSERT INTO loose VALUES (7), ('123'), ('abc');
SELECT n, typeof(n) FROM loose ORDER BY rowid;

Output

typeof(42) | typeof(4.2) | typeof('42') | typeof(NULL) | typeof(x'CAFE')
-----------+-------------+--------------+--------------+----------------
integer    | real        | text         | null         | blob

n   | typeof(n)
----+----------
7   | integer
123 | integer
abc | text

'123' was converted to an integer, but 'abc' was stored as text in an INTEGER column. A STRICT table (SQLite 3.37+) behaves like other databases:

SQL

CREATE TABLE strict_t (n INTEGER) STRICT;
INSERT INTO strict_t VALUES ('abc');

Error

cannot store TEXT value in INTEGER column strict_t.n

Choosing types

What is the difference between DECIMAL and FLOAT?

What is the difference between VARCHAR and TEXT?