The question VARCHAR or TEXT, INT or INTEGER, DATE or TEXT is not a preference — it is 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.
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 email address has a natural maximum — 254 characters is the practical limit in the SMTP standard — so a column declared VARCHAR(254) states a rule the database will enforce on every write. If someone 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; a TEXT column of the same table always carries the overhead of a large-value reference. In PostgreSQL and SQL Server there is no storage penalty at all — VARCHAR(n) and TEXT behave identically — and the limit is purely a constraint. The advice that follows from this: 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?
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 9
Ten before nine before a hundred — alphabetical, not numerical. The same values stored as INTEGERs sort as nine, ten, 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, inexplicably wrong: the values look right in every preview and behave wrongly in every comparison.
What is stored when you store a number?
Ask the engine rather than assume. The typeof function 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 text
Three different answers for three different intentions. The price is a real number, the stock is an integer, and the added_on column — declared TEXT — is text, 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 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 approximations drifts. For a single price that is harmless; for a running total over thousands of rows it is not, which is why financial systems store money in integer minor units — 4250 for forty-two pounds fifty — or in a DECIMAL column with a fixed scale.
SQLite's REAL affinity 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 real
The 7.9 was written into an INTEGER column and 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 column's declared type 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 — a quiet widening or a loud rejection.
What are the practical rules?
- Use an integer type for anything you count, and a decimal or 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, join differently and add differently.
- Choose widths that will not need changing: widening an INTEGER to BIGINT is a table rewrite in most engines.
Quick reference
- VARCHAR(n) adds a length rule; TEXT does not. The rule matters only where the engine enforces it.
- Use an integer type for counts, a decimal or minor units for money, and never store numbers as text.
- A date in SQLite is TEXT — keep it in the ISO YYYY-MM-DD form so it sorts correctly.
- REAL columns widen silently when a fractional value arrives; strict engines reject it instead.
- Pick widths that will survive: enlarging a column is a rewrite in most 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, arithmetic — that choice is really making.