Every column declares what it can hold and how much space that costs. Push values past the edge of a type, watch FLOAT lose money that DECIMAL keeps, and see why the right type is a correctness decision, not a formality.
Overview
Quick Context
A datatype declares what a column may contain. The database uses it to reject invalid data, to decide how many bytes each value occupies, and to choose how comparisons and arithmetic behave. Get it wrong and you pay in storage, in speed, or — worst — in silently incorrect numbers.
Datatypes: The Contract Every Column Signs
Choosing a type decides what values are legal, how much disk they consume, and whether your arithmetic is exact.
Integers: pick the smallest that fits
SMALLINT holds ±32,767 in 2 bytes. INT holds about ±2.1 billion in 4. BIGINT holds roughly ±9.2 quintillion in 8.
The reason to care is not the bytes themselves; it is that narrower columns mean more rows per page, which means fewer disk reads and better cache use on tables with millions of rows.
The reason to care in the other direction is famous: an INT primary key runs out at about 2.1 billion rows, and several well-known services have had emergency outages when a busy id column hit that ceiling. For any table that could plausibly grow, BIGINT for the key is cheap insurance. For a country_id referencing 200 countries, SMALLINT is plenty.
Never Store Money in FLOAT
FLOAT and DOUBLE are binary floating point. They cannot represent 0.1 exactly, for the same reason base 10 cannot write 1/3 exactly. Errors are tiny individually and accumulate over millions of rows.
The demo above adds 0.10 + 0.20 in both types. FLOAT does not produce 0.30 — it produces something imperceptibly different, and = 0.30 is then false. DECIMAL(10,2) stores the digits themselves and is exact.
Rule: money, quantities and anything a human will audit go in DECIMAL / NUMERIC. Reserve FLOAT for measurements where a rounding error in the fifteenth digit genuinely does not matter.
CHAR vs VARCHAR vs TEXT
CHAR(n) — fixed width. Always consumes n characters, padding with spaces. Only sensible when values really are uniform, like a 2-letter country code.
VARCHAR(n) — variable width up to n, storing only what you use plus a small length prefix. The sane default.
TEXT — unbounded. Convenient, but some engines store it separately from the row, which can slow reads.
The n in VARCHAR(n) is mostly a constraint, not an allocation — declaring VARCHAR(255) everywhere does not waste space, but it also fails to document what the column really holds.
Dates, Times and the NULL Question
Store dates in DATE / TIMESTAMP, never in a string. Only a real date type gives you correct sorting, date arithmetic, and rejection of 2024-02-31. For anything spanning time zones, prefer TIMESTAMP WITH TIME ZONE.
Finally, every type also permits NULL unless you say NOT NULL. NULL means unknown, which is genuinely different from 0 or an empty string — and it propagates through arithmetic and comparisons in ways that surprise people.
Choosing a type is choosing a set of guarantees
A column's type is not decoration. It decides what can be stored, how much space each row takes, which comparisons are legal, and which bugs are impossible.
Get it right and the database rejects nonsense before it enters. Get it wrong and you spend years writing code to work around a decision made in five seconds.
The families, and the decision inside each:
Family
Types
The decision
Integers
SMALLINT, INT, BIGINT
Smallest that fits the real range
Exact decimals
NUMERIC / DECIMAL
Precision and scale, for money
Floating point
REAL, DOUBLE PRECISION
Only for measurements, never money
Text
CHAR, VARCHAR, TEXT
Fixed or variable, with or without a limit
Dates and times
DATE, TIME, TIMESTAMP, TIMESTAMPTZ
With or without a time zone
Boolean
BOOLEAN
Three states, counting NULL
Structured
JSON/JSONB, arrays, UUID, ENUM
Flexibility versus enforcement
Never store money in a float
This is the single most consequential type decision most applications make.
Floating point stores numbers in binary, and 0.1 has no exact binary representation, exactly as 1/3 has no exact decimal one. The error is tiny per operation and accumulates across millions of them, which is how a ledger ends up a few pence out and nobody can say where.
Use NUMERIC(12, 2) — twelve digits in total, two after the point — for currency. It is exact, it compares reliably, and it is slower in a way no financial application will ever notice.
The common alternative is storing minor units (pence, cents) as a BIGINT, which is exact by construction and avoids decimal arithmetic entirely. Both are correct; pick one and be consistent, because mixing them across a codebase causes its own class of bug.
Floats are the right choice for genuine measurements — temperatures, distances, sensor readings — where a relative error of 10⁻¹⁵ is irrelevant and the speed matters.
Text, and the CHAR/VARCHAR/TEXT question
CHAR(n) is fixed length and pads with spaces — useful for genuinely fixed codes such as a two-letter country code, and a nuisance everywhere else because the padding turns up in comparisons.
VARCHAR(n) is variable length with a maximum. TEXT is variable length with no limit.
In PostgreSQL, VARCHAR(n) and TEXT are stored identically and perform identically — the length is a constraint, not an optimisation. So the question becomes: is n a real business rule? A country code of exactly 2, yes. A VARCHAR(255) for a name is an inherited habit from old MySQL row formats and constrains nothing meaningful.
In MySQL the picture is a little different: VARCHAR columns participate in the row size limit and index prefix rules in ways TEXT does not, so the choice has more consequences there.
Two related choices worth making deliberately: the collation, which decides sorting and case sensitivity, and whether to store emails and usernames in a case-insensitive type such as PostgreSQL's CITEXT, which prevents "Alice@example.com" and "alice@example.com" being two different users.
What the engine does with a type you declared
A column's declared type is a promise about what goes in it, and how strictly that promise is kept differs sharply between engines. SQLite is the permissive end of that range, which makes it a useful place to see what a type is actually for.
query.sqlSQLite
-- SQLite has five storage classes: NULL, INTEGER, REAL, TEXT, BLOB.
-- The declared type is a hint it uses to decide which one to store in.
CREATE TABLE t (
i INTEGER,
r REAL,
txt TEXT,
anything -- no declared type at all, which SQLite permits
);
INSERT INTO t VALUES (42, 42, 42, 42);
INSERT INTO t VALUES ('42', '42', '42', '42');
INSERT INTO t VALUES ('abc', 'abc', 'abc', 'abc');
SELECT i, typeof(i) AS i_is,
r, typeof(r) AS r_is,
txt, typeof(txt) AS txt_is,
anything, typeof(anything) AS any_is
FROM t;
-- Read row 2 across. The same string '42' was inserted into all four
-- columns. In the INTEGER column it came back as an INTEGER -- SQLite
-- converted it, because the value was convertible. In the untyped
-- column it stayed TEXT, because nothing asked it to convert.
-- Row 3 put 'abc' into the INTEGER column and it stayed TEXT too: the
-- conversion is attempted, and declined when it cannot succeed.
--
-- That is type AFFINITY: a preference rather than a rule. PostgreSQL
-- would have rejected row 3 outright. Neither behaviour is wrong, and
-- code written against one surprises you on the other.
--
-- The practical consequences are in comparisons.
SELECT '42 = 42' AS test, (42 = 42) AS result
UNION ALL SELECT 'quoted 42 vs 42', ('42' = 42)
UNION ALL SELECT '1 = true', (1 = TRUE)
UNION ALL SELECT 'null = null', (NULL = NULL)
UNION ALL SELECT 'null is null', (NULL IS NULL);
-- Look at 'quoted 42 vs 42' in that table: it is FALSE. A TEXT '42'
-- and an INTEGER 42 are not equal, because no column affinity applies
-- to a comparison between two literals -- which is how a query that
-- works against one column silently returns nothing against another.
-- And note that null = null is neither true nor false: it is NULL.
--
-- Now dates, which SQLite does not have as a type at all. They are
-- TEXT in ISO-8601 order, which sorts and compares correctly precisely
-- because that format sorts lexicographically.
SELECT placed,
date(placed, '+30 days') AS due,
strftime('%Y-%m', placed) AS month,
julianday('2024-04-02') - julianday(placed) AS days_ago
FROM orders
ORDER BY placed
LIMIT 3;
-- Storing dates as '02/04/2024' instead would break every one of those
-- and the ORDER BY as well. The format is doing the work.
Result
Experiments to try
Select TINYINT and enter 200. Rejected — out of range. Now try 100, which fits comfortably.
Select INT and enter 3000000000. Overflow again, at exactly the boundary that has broken many real systems.
Compare the storage figures. Switching a column from BIGINT to INT on a billion-row table saves four gigabytes.
Read the money demo closely. The FLOAT sum is not 0.30, and the equality test fails. That is not a bug in this page — it is how binary floating point works everywhere.
Select CHAR(10) and type a short word to see how much of the fixed width is wasted on padding.
Worth remembering
Pick the narrowest type that will hold every legitimate value, use DECIMAL for anything monetary, use real date types for dates, and add NOT NULL wherever a missing value would be meaningless. These choices are hard to change later and quietly govern correctness forever.
Dates, times and time zones
This is where subtle bugs live, and one distinction prevents most of them.
TIMESTAMP WITHOUT TIME ZONE stores a wall-clock reading with no idea where in the world it was taken. Two servers in different regions will interpret the same stored value differently.
TIMESTAMPTZ (with time zone) converts the input to UTC on the way in and back to the client's zone on the way out. It represents an actual moment in time, unambiguously.
For anything that records when something happened, use the time-zone-aware type and store UTC. Convert to local time for display only, at the edge of the system.
DATE for birthdays and anniversaries is correct precisely because they are not moments in time — a birthday does not shift when you fly to another country.
Two further traps. BETWEEN '2026-01-01' AND '2026-01-31' on a timestamp column silently excludes everything after midnight on the 31st; use >= '2026-01-01' AND < '2026-02-01'. And storing a duration as a timestamp is a category error — use INTERVAL or an integer number of seconds.
JSON, arrays and enums
Modern relational engines carry types that used to be reasons to leave.
JSONB (PostgreSQL) stores a parsed, indexable JSON document. It is genuinely useful for variable payloads — event bodies, per-tenant settings, third-party API responses. It becomes a mistake when fields you regularly filter, sort or join on are buried inside it: those belong in columns, where they can be typed, constrained and indexed properly. A reasonable rule is to promote a field to a column as soon as it appears in a WHERE clause twice.
Arrays hold several values in one column. Convenient for tags, and a trap for anything that will later need its own attributes — the moment you want "when was this tag added", you need a junction table.
ENUM constrains a column to a fixed list. It documents intent and is compact, but adding a value requires a schema change in most engines. A small lookup table with a foreign key is more flexible and usually the better long-term choice.
UUID as a primary key trades sortability and 16 bytes for the ability to generate ids on the client without coordination. Random UUIDs fragment B-tree indexes because inserts land everywhere; UUIDv7, which is time-ordered, avoids that and is the version to prefer for keys.
Questions people ask
Does the type affect index size? Yes, directly. A BIGINT index is twice the size of an INT one, and a TEXT index on long values can be enormous.
Should I use BOOLEAN or a 0/1 integer?BOOLEAN where the engine has it — it is self-documenting and compact. Remember it has three states with NULL.
What happens if a value overflows? A proper engine raises an error. MySQL in non-strict mode historically truncated silently, which is why strict mode matters.
Can I change a column's type later? Yes, with ALTER TABLE, but it typically rewrites the table and holds a lock — expensive on large tables and a planned operation on live systems.
Is VARCHAR(50) faster than VARCHAR(500)? Not in PostgreSQL, where storage is identical. The limit is a constraint; use it to express a real rule, not a guess.
How should I store phone numbers? As text, always. They have leading zeros, plus signs and country prefixes, and no arithmetic is ever performed on them.
Recap in one screen
Types are constraints: they decide what can be stored and which errors are impossible.
NUMERIC or minor-unit integers for money; floats only for measurements.
Pick the narrowest integer that fits, but use BIGINT for keys on tables that could grow.
TIMESTAMPTZ in UTC for moments in time; DATE for calendar dates.
JSONB for genuinely variable data; promote anything you filter on into a real column.
Recall check
0 of 3
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
Without scrolling back — what is the one-line takeaway from this module?
Pick the narrowest type that will hold every legitimate value, use DECIMAL for anything monetary, use real date types for dates, and add NOT NULL wherever a missing value would be meaningless. These choices are hard to change later and quietly govern correctness forever.
What does this module say about “Quick Context”?
A datatype declares what a column may contain. The database uses it to reject invalid data, to decide how many bytes each value occupies, and to choose how comparisons and arithmetic behave. Get it wrong and you pay in storage, in speed, or — worst — in silently incorrect numbers.
What does this module say about “Integers: pick the smallest that fits”?
SMALLINT holds ±32,767 in 2 bytes. INT holds about ±2.1 billion in 4. BIGINT holds roughly ±9.2 quintillion in 8.
Cheat sheet
Datatypes in SQL
Every column declares what it can hold and how much space that costs. Push values past the edge of a type, watch FLOAT lose money that DECIMAL keeps, and see why the right type is a correctness decision, not a formality.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.