Choosing Data Types

A column's type is the first constraint on it. Get it right and the database rejects nonsense for free; get it wrong and every query has to compensate forever.

The families

Integers — SMALLINT (±32k), INTEGER (±2.1 billion), BIGINT (±9.2 quintillion). Use INTEGER by default. Use BIGINT for identity columns on anything that might grow — running out of 32-bit ids in production is a famous and entirely avoidable outage.

Exact decimals — DECIMAL(p, s) / NUMERIC(p, s), where p is total digits and s digits after the point. DECIMAL(10, 2) holds up to 99,999,999.99. This is what money uses.

Floating point — REAL, DOUBLE PRECISION. Fast, approximate, and wrong for money:

SQL▶ Run
SELECT 0.1 + 0.2          AS floating_point_sum,
       0.1 + 0.2 = 0.3    AS is_it_equal_to_point_three;

The sum is 0.30000000000000004 and the equality is false. Binary floating point cannot represent 0.1 exactly, so the error compounds. Across a million transactions, a financial report that does not balance is the result — and the cause is a column type chosen years earlier.

Use DECIMAL for money, or store integer minor units (pence, cents) and divide for display. Never FLOAT.

Text — CHAR(n) fixed-length and space-padded (essentially only for genuine fixed codes), VARCHAR(n) variable up to a limit, TEXT unlimited. On PostgreSQL VARCHAR and TEXT perform identically, so the length is a constraint, not an optimisation. On MySQL and SQL Server the choice affects storage and indexing more.

Pick a length only where the domain has one. VARCHAR(255) is not a considered choice, it is a habit inherited from old MySQL — and a surname column that rejects a real surname is a bug people notice.

Dates and times — DATE, TIME, TIMESTAMP, and crucially TIMESTAMP WITH TIME ZONE (timestamptz on PostgreSQL). Store instants with a time zone; store a plain DATE only for genuine calendar dates such as a birthday. The alternative is discovering during a daylight-saving change that an hour of records is ambiguous.

Boolean — a real BOOLEAN where it exists. MySQL's BOOLEAN is an alias for TINYINT(1); SQL Server uses BIT; Oracle only added a real boolean in 23c.

JSON — JSON/JSONB (PostgreSQL), JSON (MySQL 5.7+). Genuinely useful for irregular attributes. It is not a substitute for columns: anything you filter, join or sort on should be a column, because that is what can be constrained and indexed cheaply.

UUID — a native type on PostgreSQL. As a primary key it makes ids unguessable and generatable client-side, at the cost of random insert order, which fragments B-tree indexes. UUIDv7 (time-ordered) fixes that and is the right choice if you want UUID keys today.

SQLite is different, and it matters here

The playground runs SQLite, which has dynamic typing: a column's declared type is a suggestion, and each value carries its own type.

SQL▶ Run
SELECT typeof(emp_id)     AS id_type,
       typeof(first_name) AS name_type,
       typeof(salary)     AS salary_type,
       typeof(email)      AS email_type
FROM employees
WHERE emp_id = 4;

That employee's email is NULL, so its type is null rather than text — the type belongs to the value, not the column. No other mainstream database behaves this way. It is why SQLite is forgiving in the playground and why you should not generalise its type behaviour to PostgreSQL or SQL Server.

Rules worth following

1. The smallest type that fits the domain. Narrower rows mean more per page and less I/O — but never so narrow it will break.
2. DECIMAL for money. Always.
3. Time zone-aware timestamps for instants.
4. NOT NULL unless NULL means something specific. Lesson 14 showed what NULLs cost in every query afterwards; the type declaration is where you prevent them.
5. Match types across foreign keys. A BIGINT referencing an INTEGER invites the implicit-conversion index failure from lesson 36.
6. Do not store numbers as text. '10' < '9' alphabetically, and no index will save a query that has to cast.

Changing a type later

Not free. Depending on the database and the change it may rewrite the entire table while holding a lock — an outage on a large table. Widening within a family (INTEGER to BIGINT) is usually cheap on PostgreSQL 11+; changing a family is not. This is the argument for thinking about it at design time rather than migration time.

Common mistakes

• FLOAT for money — the error compounds and cannot be recovered.
• VARCHAR(255) everywhere — a default, not a decision.
• Timestamps without time zones — ambiguous twice a year.
• Numbers or dates stored as text — no ordering, no validation, no index.
• INT for an id column on a growing table — a predictable, dateable outage.

Interview question

Why should money never be stored as a float?

Binary floating point cannot represent decimal fractions like 0.1 exactly, so arithmetic accumulates error and totals stop reconciling. DECIMAL/NUMERIC is exact; integer minor units are the other correct option. 0.1 + 0.2 <> 0.3 is the demonstration, and it is one line.

Check yourself

1. What type would you use for a price, and why not REAL?
2. Why is TIMESTAMP WITH TIME ZONE preferable for an event time?
3. What does SQLite's typeof() reveal about how it stores types?