Choosing a column type
Which integer, which string, which way to store money and time — with the MySQL, PostgreSQL, Oracle and SQL Server equivalents side by side.
Integers, and the one that bites
An integer type is a promise about the largest number you will ever store. The promise that gets broken most often is smallint, because 32,767 sounds enormous until it is a display order, a page count or a quantity on a busy table.
| Type | Range (signed) | Bytes | Use for |
|---|---|---|---|
| smallint | −32,768 … 32,767 | 2 | Enumerated codes, small fixed sets |
| integer / int | ±2.1 billion | 4 | Almost everything, including sort orders |
| bigint | ±9.2 quintillion | 8 | Primary keys on anything that grows, money in minor units |
Sort and display order columns are the usual casualty. They look like they will hold small numbers, and then someone reorders by inserting gaps of 100 and the values climb past the limit in a year. Use integer and stop thinking about it.
Strings
In PostgreSQL, varchar(n) and text are the same type with a length check bolted on, and the check costs nothing. Use text unless the length is a real rule — a country code is two characters and should say so; a product name has no natural limit and a 255 on it is a guess that will be wrong.
In MySQL the choice matters more, because an index on a long varchar has a key length limit and text columns are stored differently. There, sizing is a real decision rather than documentation.
| Need | PostgreSQL | MySQL | Oracle | SQL Server |
|---|---|---|---|---|
| Fixed-length code | char(2) | char(2) | char(2) | char(2) |
| Bounded text | varchar(255) | varchar(255) | varchar2(255) | nvarchar(255) |
| Unbounded text | text | text / longtext | clob | nvarchar(max) |
| Identifier | uuid | binary(16) / char(36) | raw(16) | uniqueidentifier |
Money is never a float
Floating point cannot represent 0.1, so a sum of prices drifts. The drift is invisible in testing and shows up as a one-cent discrepancy in a monthly report that someone has to explain. Two options are correct: a fixed-point decimal, or an integer count of the smallest unit.
| Approach | PostgreSQL | MySQL | Good for | Watch out |
|---|---|---|---|---|
| Fixed point | numeric(19,4) | decimal(19,4) | Prices, totals, tax | Slower arithmetic than integers |
| Minor units | bigint | bigint | Ledgers, payment providers | Every read and write must agree on the scale |
| Rates and shares | numeric(7,4) | decimal(7,4) | Discount rates, commission | Needs enough scale for the rounding rule |
Pick the scale from the rounding rule, not from the currency. A discount of 12.5% stored as numeric(5,2) can hold 12.50, but a rate that is computed rather than entered — a commission split three ways — needs the extra places or it rounds at the wrong step.
Timestamps and time zones
There are two kinds of moment and they need different types. An event that happened — created_at, paid_at, logged_in_at — is a point on the world's timeline and belongs in a type that carries a zone. A wall-clock intention — a shop opens at 09:00, a reminder at 08:00 local — is not a point on that timeline until you know where, and storing it with a zone silently commits it to the server's.
| PostgreSQL | MySQL | Oracle | SQL Server | |
|---|---|---|---|---|
| Event in time | timestamptz | timestamp | timestamp with time zone | datetimeoffset |
| Wall clock | timestamp | datetime | timestamp | datetime2 |
| Date only | date | date | date | date |
| Duration | interval | — | interval | — |
The default should be the zoned type. Almost every column in a normal schema records something that happened, and the cost of getting it wrong is a bug that only appears for users in another country or twice a year at a daylight-saving boundary.
Booleans, enums and the third state
A boolean column with two values is fine right up until it needs a third. is_approved on a comment starts as true or false and then someone asks for 'pending', and the column becomes is_approved plus is_pending, which can both be true.
- If the answer is genuinely yes or no forever — is_active, is_deleted — use boolean.
- If it is a state in a lifecycle — draft, published, archived — use a status column from the start, even with two values.
- Store the status as text with a check constraint rather than a database enum: adding a value to a Postgres enum is a migration, adding one to a check constraint is also a migration but reads better and can be dropped.
- Never use a nullable boolean to mean three things. Null is 'unknown', and every query that filters on the column has to remember it.
Frequently asked
- Should I use int or bigint for a primary key?
- bigint, on anything that could grow. Two billion sounds like plenty until a table of events, logs or line items reaches it, and changing a primary key type on a live table with foreign keys pointing at it is one of the more painful migrations there is.
- varchar(255) or text?
- In PostgreSQL, text, unless 255 is a real rule. The number comes from an old MySQL index limit and carries no meaning in Postgres. In MySQL it is a genuine decision because of index key lengths.
- How should I store money?
- numeric/decimal with a scale chosen from your rounding rule, or an integer count of minor units. Never float or double — the error is invisible until it is a reconciliation problem.
- timestamptz or timestamp?
- timestamptz for anything that happened. timestamp without a zone for a wall-clock intention like opening hours, where the zone belongs to the place rather than to the moment.
- Is a database enum better than a check constraint?
- Rarely. Both need a migration to change. A check constraint on a text column is readable in a query result, works the same in every dialect, and can be removed without rewriting the column.