VARCHAR or TEXT, INT or INTEGER, DATE or TEXT — these are not personal preferences. They are four separate decisions about length, range, meaning and arithmetic. Get the type right and the database enforces the rule for you. Get it wrong and you inherit a column of strings where numbers were expected, with every comparison quietly misbehaving.
The previous lesson in this module covered "How Does CREATE TABLE Choose the Right Types?", and the next lesson in this module is "What Makes a Good Primary Key?".
VARCHAR vs TEXT vs INT vs DATE: which type fits?
Each of these four types answers a different question. VARCHAR asks "how long is this string, at most?". TEXT asks "is this a string?". INT asks "is this a whole number in a known range?". DATE asks "is this a point on the calendar?".
Here is how they compare across the engines you are most likely to meet.
| Type | What it holds | Length limit | Notes across engines |
|---|---|---|---|
| VARCHAR(n) | text up to n characters | yes, n | MySQL enforces n; PostgreSQL and SQL Server treat it as a check; SQLite ignores it |
| TEXT | text of any length | no practical limit | identical idea in every engine; SQL Server also has VARCHAR(MAX) |
| INT | whole number | no | PostgreSQL INTEGER is 4 bytes, MySQL INT is 4 bytes, SQL Server INT is 4 bytes |
| BIGINT | whole number, larger range | no | 8 bytes; use it for identifiers that will grow |
| DECIMAL(p,s) | exact fractional number | no | SQLite has no DECIMAL; NUMERIC affinity is the closest |
| REAL / FLOAT | approximate fraction | no | floating point rounding makes exact money comparisons risky |
| DATE | a calendar date | no | a real type in MySQL, PostgreSQL and SQL Server; only text in SQLite |
| TIMESTAMP | a date and time of day | no | timezone handling varies widely between engines |
When does the length limit matter?
It matters when the limit is part of the rule.
A customer's email address has a natural maximum, because 254 characters is the practical limit in the SMTP standard. A column declared VARCHAR(254) therefore states a rule that the database will enforce on every write. If somebody tries to store a 300-character address, the insert fails immediately, instead of producing a value that breaks an email library three layers up.
But the length limit is a double-edged tool.
In MySQL a VARCHAR(50) column stores short values efficiently and long ones with an overflow pointer, while a TEXT column in the same table always carries the overhead of a large-value reference. In PostgreSQL and in SQL Server there is no storage penalty at all, so VARCHAR(n) and TEXT behave identically, and the limit is purely a constraint.
The advice that follows from this is simple. Declare the limit when the business has one, and do not invent limits for columns where it has none.
Why is storing a number as text a bug?
It is a bug because the storage type decides the comparison, and text compares character by character.
Three values that look like numbers in a spreadsheet sort in the wrong order the moment they are stored as characters.
CREATE TABLE sort_probe (id INTEGER, label TEXT);
INSERT INTO sort_probe VALUES (1,'9'),(2,'10'),(3,'100');
SELECT id, label FROM sort_probe ORDER BY label;id label
-- -----
2 10
3 100
1 9Ten before nine before a hundred. That is alphabetical order, not numerical order.
The same values stored as INTEGERs sort as nine, ten and a hundred, and they sum as numbers rather than as concatenated characters.
This is the practical reason behind the rule, and it explains why an imported CSV full of quoted prices causes reports that are subtly and inexplicably wrong. The values look right in every preview, and they behave wrongly in every comparison.
What is stored when you store a number?
You ask the engine rather than assume. The function typeof reports the storage class of a value as SQLite sees it.
SELECT sku, price, stock, typeof(price) AS price_type, typeof(stock) AS stock_type, typeof(added_on) AS date_type
FROM products LIMIT 5;sku price stock price_type stock_type date_type
------ ----- ----- ---------- ---------- ---------
TB-001 42.5 18 real integer text
TB-002 9.95 120 real integer text
TB-003 24.0 35 real integer text
TB-004 31.75 22 real integer text
TB-005 58.0 12 real integer textThere are three different answers for three different intentions. The price is a real number, the stock is an integer, and the added_on column, which is declared TEXT, is text and not a date.
That last one is the decision this module keeps returning to. SQLite has no DATE type, so a date stored as TEXT is stored as an ISO string, and it sorts chronologically only because the format is fixed-width and zero-padded.
Store dates in the ISO form and that works. Store '4 March 2025' and it does not.
Why is a REAL column not a money column?
Floating point cannot represent most decimal fractions exactly.
Storing 42.50 as a REAL stores the nearest binary approximation, and arithmetic on the approximations drifts. For a single price that is harmless. For a running total over thousands of rows it is not. That is why financial systems store money in integer minor units, so 4250 for the forty-two pounds fifty, or in a DECIMAL column with a fixed scale.
The REAL affinity of SQLite makes the coercion visible.
CREATE TABLE probe (id INTEGER, label TEXT, price REAL, quantity INTEGER);
INSERT INTO probe VALUES (3, 'real money', 42.999, 7.9);
SELECT id, price, typeof(price) AS price_type, quantity, typeof(quantity) AS qty_type FROM probe WHERE id = 3;id price price_type quantity qty_type
-- ------ ---------- -------- --------
3 42.999 real 7.9 realThe 7.9 was written into an INTEGER column, and it came back as the real 7.9, because SQLite's INTEGER affinity stores a whole number when it can and promotes to a REAL when it cannot.
The value is preserved exactly here, but the promotion is the tell. The declared type of the column no longer describes every value in it. A stricter engine would have refused the 7.9 outright, and the choice you make about types decides which failure you get. You get either a quiet widening or a loud rejection.
What are the practical rules?
- Use an integer type for anything you count, and a decimal or an integer-minor-units type for money.
- Use TEXT for anything you read as characters, with a VARCHAR limit only when the business has one.
- Use a real DATE or TIMESTAMP type where the engine has one. Use ISO TEXT where it does not, and never mix the two in one column.
- Never store a number as text. A text
'9'and the number 9 sort differently, they join differently, and they add differently. - Choose widths that will not need changing, because widening an INTEGER to a BIGINT is a table rewrite in most of the engines.
Quick reference
VARCHAR(n)adds a length rule and TEXT does not. The rule matters only where the engine enforces it.- Use an integer type for counts, a decimal or the minor units for money, and never store numbers as text.
- A date in SQLite is text, so keep it in the ISO YYYY-MM-DD form and it sorts correctly.
- REAL columns widen silently when a fractional value arrives, and the strict engines reject it instead.
- Pick widths that will survive, because enlarging a column is a rewrite in most of the engines.
Type drill: for each column in the shop's products table, name the type you would choose in a strict engine, and say which of the four decisions — length, range, meaning and arithmetic — that choice is really making.