SQL types and the type system
Review a table's types
The idea in 30 seconds
Review column types by asking what each value means, not what it looks like. Money is an exact decimal; a moment in time is a timestamptz; a yes/no answer is a boolean; a phone number is text; an id that may grow is a bigint. Every mismatch is a bug waiting for the right data.
Helps to know first: SQL data modification
On this page
The situation
A payments feature needs a table. An AI assistant proposes one.
Review taskPayments table
Pull request: “Add payments table.”
CREATE TABLE payments (
id serial PRIMARY KEY,
amount float,
paid_at timestamp,
customer_phone integer,
refunded varchar(5) -- 'true' or 'false'
);Would you approve it? Write the review you would leave.
Your call
Model review
Request changes. Every column except the key has a type that will corrupt or reject real data. We loaded sample rows into exactly this table to check.
1. amount float cannot hold money exactly. Floating point is approximate: ten payments of 0.10 added up to 0.9999999999999999 in our test, and 0.1 + 0.2 = 0.3 is false. Use numeric(12, 2) (or an integer number of the smallest currency unit), and store the currency alongside it.
2. paid_at timestamp throws away the time zone. We inserted '2025-03-02 10:15+05:30'; the column kept 2025-03-02 10:15:00 and silently dropped the +05:30. Payments from different time zones can no longer be put in order. Use timestamptz.
3. customer_phone integer rejects real phone numbers. Inserting a 10-digit Indian mobile number failed with ERROR: integer out of range, because integer stops at 2,147,483,647. A bigint would accept it but still lose leading zeros and reject +91. A phone number is an identifier: use text.
4. refunded varchar(5) accepts anything up to five characters. In our test it happily stored FALSE, no and False as three different values, and every query has to guess which spellings mean no. Use boolean NOT NULL DEFAULT false.
5. serial is a 4-byte integer. A payments table can outgrow 2.1 billion rows. Use bigint GENERATED ALWAYS AS IDENTITY.
6. Nothing is required. A payment with no amount or no time is not a payment. Add NOT NULL to every column that must have a value.
What I would ask for:
CREATE TABLE payments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount numeric(12, 2) NOT NULL CHECK (amount > 0),
currency char(3) NOT NULL,
paid_at timestamptz NOT NULL,
customer_phone text,
refunded boolean NOT NULL DEFAULT false
);Compare it with yours. Did your review catch the main problems?
How to review a column’s type
For each column, ask what the value means:
| If the value is… | Use | Not |
|---|---|---|
| An amount of money | numeric(p, s) | real, double precision |
| A moment something happened | timestamptz | timestamp, text |
| A calendar day | date | timestamptz at midnight |
| Yes or no | boolean | text, 0/1 integers |
| An identifier made of digits (phone, postcode, account number) | text | Any number type |
| A row id that might grow large | bigint | integer, serial |
| Text with a real business length limit | varchar(n) | text with checks scattered in code |
Check yourself
Which type would you use for a payment amount, and why?
Show answer
numericwith a fixed scale, such asnumeric(12, 2). It stores decimal values exactly, so totals and comparisons are correct. Floating-point types are approximate.What is lost when
'2025-03-02 10:15+05:30'is stored in atimestampcolumn?Show answer
The
+05:30offset. The column keeps only the clock reading10:15, so the exact instant can no longer be known.Why is
booleanbetter thanvarchar(5)for a refunded flag?Show answer
A
booleanholds only true, false or null, so every row means the same thing. Free text accepts many spellings, and every query has to guess which ones mean yes.
Finished reading?
Saved in this browser only.
Sources
Where each claim on this page comes from.
- 1.PostgreSQL documentation: 8.1 Numeric TypesPrimary· §8.1 Numeric Types
Integer ranges; numeric is exact and recommended for money; floating-point types are inexact.
- 2.PostgreSQL documentation: 8.5 Date/Time TypesPrimary· §8.5.1.3 Time Stamps
timestamp without time zone silently ignores a time zone given in the input.
- 3.PostgreSQL documentation: 8.6 Boolean TypePrimary· §8.6 Boolean Type
The boolean type stores true, false or null, and accepts several input spellings for each.
- 4.PostgreSQL documentation: ALTER TABLEPrimary· Notes
Changing the type of an existing column normally rewrites the entire table and its indexes.
Go further
What to read next, and why it is worth the time.
- 1.PostgreSQL documentation: 8.1 Numeric TypesPrimary· §8.1
Worth knowing the full table of numeric types and their exact limits before you design your next table.
Applies to PostgreSQL 18. Draft: not yet fact-checked end to end.