Data Types and Domains
Pedal stored rental prices as real, and the monthly revenue report came out as 18204.699999. Accountants noticed. The type you give a column decides what can be stored, how it compares and sorts, and how much space it takes, so choosing it is a design decision, not a formality.
The rule
Pick the narrowest type that holds every real value exactly.
In relational terms, a column's set of allowed values is its domain. In PostgreSQL the domain comes first from the data type, and you can narrow it further.
The types a DBA meets daily:
| Need | Type | Note |
|---|---|---|
| Ids and counts | integer, bigint |
Prefer bigint for ids that may grow |
| Money and exact decimals | numeric(10,2) |
Exact; never real for money |
| Measurements | double precision |
Fast, approximate |
| Text | text, varchar(n) |
Same speed; varchar(n) only adds a length limit |
| Yes or no | boolean |
|
| Moments in time | timestamptz |
Stores an absolute instant |
| Documents | jsonb |
Indexable JSON |
| Identifiers from outside | uuid |
PostgreSQL also lets you name a reusable restricted type with CREATE DOMAIN:
CREATE DOMAIN price_inr AS numeric(8,2)
CHECK (VALUE >= 0);
CREATE TABLE tariffs (
city text PRIMARY KEY,
per_hour price_inr NOT NULL,
day_cap price_inr NOT NULL
);Both columns now share one definition of "a valid price". Change the rule once and every column using the domain follows.
The shortcut
For each column ask two questions: "Must it be exact?" and "What is the biggest honest value?" Exact → numeric or an integer type. Then pick the smallest type that fits the biggest value with room to grow.
rental_id
Exact, and could pass two billion over the years:
bigint.
The trap is timestamp without time zone for real events. It stores a wall-clock reading with no zone, so two cities' "10:00" look identical though they are hours apart.
Drills
Answer fast — trust the rule.
Which type should hold a rental's fare?
What does varchar(50) give you over text in PostgreSQL?
What is a PostgreSQL domain?
A column records when a bike was unlocked, for members in several time zones. Which type fits?
Remember
- Pick the narrowest type that holds every real value exactly.
- Must it be exact? What is the biggest honest value? Then choose.
- Use domains for rules shared by many columns; use
timestamptzfor real events.