Every column has a type, and the type decides what values are allowed, how much space they take, which operators and indexes work on them and how they sort. Picking good types up front avoids most data-quality bugs. This lesson tours the types you will use daily and adds a subscription plan to Orbit’s users.
The short list
| Kind | Use | Avoid |
|---|---|---|
| Whole numbers | bigint for ids and counters, integer for small counts | smallint unless space really matters |
| Exact decimals | numeric(12,2) for money and quantities | real / double precision for money |
| Approximate | double precision for measurements and science | |
| Text | text, with a CHECK if you need a limit | char(n) |
| Yes / no | boolean | int flags, 'Y'/'N' strings |
| Points in time | timestamptz | timestamp without time zone |
| Calendar days | date | storing dates as text |
| Durations | interval | seconds in an int |
| Identifiers | uuid when ids are created outside the database | text UUIDs |
| Flexible data | jsonb | json, unless you need the exact original text |
Numbers: exact versus approximate
integer (4 bytes) holds about ±2.1 billion and bigint (8 bytes) about ±9.2 quintillion. Postgres refuses to overflow silently:
SELECT 2147483647::integer + 1;
ERROR: integer out of range
For decimals, the choice matters more than it seems. double precision is binary floating point, fast but approximate. numeric stores decimal digits exactly:
SELECT 0.1::double precision + 0.2::double precision AS float_sum,
0.1::numeric + 0.2::numeric AS numeric_sum;
float_sum | numeric_sum
---------------------+-------------
0.30000000000000004 | 0.3
(1 row)
That is why money belongs in numeric. Give it a precision and scale, numeric(12,2), and Postgres rounds to two decimals on the way in:
SELECT 19.999::numeric(10,2);
numeric
---------
20.00
(1 row)
Dividing two integers gives an integer (7 / 2 is 3). Cast one side to numeric when you want the fraction.
Text
text, varchar and varchar(n) are stored the same way in Postgres, with no performance difference. varchar(n) only adds a length check, and that check has a surprise: an explicit cast truncates instead of failing.
SELECT 'orbital'::varchar(5);
varchar
---------
orbit
(1 row)
Inserting 'orbital' into a varchar(5) column, on the other hand, fails with value too long for type character varying(5). Most teams use plain text and add a constraint they can change later without rewriting the table:
ALTER TABLE notes ADD CONSTRAINT notes_title_length CHECK (char_length(title) <= 200);
Avoid char(n): it pads values with spaces, which then disappear or reappear depending on the operation.
Booleans
boolean holds true, false or NULL. Postgres accepts several spellings on input and always prints t or f:
SELECT 'yes'::boolean, 'off'::boolean, 't'::boolean;
bool | bool | bool
------+------+------
t | f | t
(1 row)
Dates, times and time zones
This is where most bugs hide, so learn the rule once: use timestamptz for anything that happened at a moment in time.
Despite its name, timestamptz (timestamp with time zone) does not store a time zone. It stores an absolute instant, converted to UTC on the way in, and converts it to the session’s timezone on the way out:
SET timezone = 'UTC';
SELECT timestamptz '2026-11-09 12:00:00-03' AS stored_instant;
stored_instant
------------------------
2026-11-09 15:00:00+00
(1 row)
SET timezone = 'America/Sao_Paulo';
SELECT timestamptz '2026-11-09 12:00:00-03' AS stored_instant;
RESET timezone;
stored_instant
------------------------
2026-11-09 12:00:00-03
(1 row)
Same instant, displayed for two different readers. Plain timestamp (without time zone), by contrast, is just a calendar date and wall-clock time, and silently ignores any offset you give it:
SELECT timestamp '2026-11-09 12:00:00-03' AS wall_clock;
wall_clock
---------------------
2026-11-09 12:00:00
(1 row)
date is a calendar day and interval a duration. They combine naturally:
SELECT date '2026-11-09' + 30 AS in_30_days,
interval '1 day 2 hours' * 3 AS three_times;
in_30_days | three_times
------------+-----------------
2026-12-09 | 3 days 06:00:00
(1 row)
UUIDs
A uuid is a 128-bit identifier that can be generated anywhere, by the database or by clients, without coordination. It is stored in 16 bytes, so always use the uuid type rather than text.
SELECT gen_random_uuid(); -- version 4: completely random
SELECT uuidv7(); -- version 7 (PostgreSQL 18): time-ordered
Random version-4 UUIDs scatter inserts all over a B-tree index, which hurts write performance on big tables. Version-7 UUIDs start with a timestamp, so new values land at the end of the index like an identity column. If you want UUID keys, use DEFAULT uuidv7().
Enums and domains
An enum is a fixed, ordered list of labels. Orbit has two subscription plans:
CREATE TYPE plan AS ENUM ('free', 'pro');
ALTER TABLE users ADD COLUMN plan plan NOT NULL DEFAULT 'free';
CREATE TYPE
ALTER TABLE
You can add labels later with ALTER TYPE plan ADD VALUE 'team';, but not remove them. If the list changes often, a lookup table or a CHECK (plan IN (...)) constraint is easier to evolve.
A domain is a reusable type with built-in rules:
CREATE DOMAIN email_address AS text CHECK (VALUE ~ '^[^@\s]+@[^@\s]+$');
Arrays and JSON
Any type can be an array, and jsonb stores JSON documents in a binary form you can index and query:
SELECT ARRAY['work', 'postgres'] AS tags,
'{"b": 1, "a": 2, "a": 3}'::jsonb AS doc;
tags | doc
-----------------+------------------
{work,postgres} | {"a": 3, "b": 1}
(1 row)
jsonb removes duplicate keys (the last one wins), drops insignificant whitespace and stores keys in its own order. Use json only if you must keep the original text exactly. Arrays and JSON are powerful, but they are not a replacement for tables: if you need to join on something, enforce rules on it or count it, it probably wants its own table. Lesson 8 models Orbit’s tags as a table for exactly that reason, and lesson 13 adds a jsonb settings column.
Casting
Convert between types with ::type or the standard CAST(value AS type). pg_typeof tells you what type an expression has:
SELECT pg_typeof(1), pg_typeof(1.5), pg_typeof('x'), pg_typeof(now());
pg_typeof | pg_typeof | pg_typeof | pg_typeof
-----------+-----------+-----------+--------------------------
integer | numeric | unknown | timestamp with time zone
(1 row)
A quoted literal starts out as unknown and takes whatever type the context needs, which is why WHERE created_at > '2026-10-01' just works.
Quick reference
| Need | Type |
|---|---|
| Primary key | bigint GENERATED ALWAYS AS IDENTITY or uuid DEFAULT uuidv7() |
| Money, quantities | numeric(12,2) |
| Name, title, body | text (+ CHECK for limits) |
| Flag | boolean |
| When it happened | timestamptz |
| Birthday, due date | date |
| Duration | interval |
| Fixed set of labels | enum or CHECK (... IN (...)) |
| Flexible attributes | jsonb |
| Type of an expression | pg_typeof(expr) |
Exercise
An invoice total is stored as double precision and reports show 0.30000000000000004. Which type should the column have?
Show answer
Answer: D. Floating-point types are binary approximations, so many decimal fractions cannot be stored exactly. numeric keeps exact decimal digits, which is what money needs.
Exercise
A server in São Paulo and one in Lisbon store the same event in a timestamptz column. What is stored on disk?
Show answer
Answer: B. timestamptz converts input to UTC and stores the instant. The session’s timezone setting only affects how it is parsed and displayed.
Exercise
Why are version-7 UUIDs better primary keys than random version-4 UUIDs on large tables?
Show answer
Answer: A. Random keys cause writes to touch pages all over a B-tree, which fragments it and evicts cache. Time-ordered keys behave much like an identity column.
Key takeaways
- Use
bigintfor ids,numericfor money anddouble precisiononly for approximate measurements. textis the default string type; enforce limits withCHECKconstraints.timestamptzstores instants; plaintimestampsilently ignores time zones.- Prefer
uuidv7()over random UUIDs for keys on big tables. - Enums suit short, stable lists;
jsonbsuits flexible attributes, not data you join or count.
FAQ
Does choosing varchar(255) over text save space or speed?
No. Postgres stores both the same way and only checks the length for varchar(n). The number 255 is a habit from other databases.
How do I store a time zone with an event, such as a meeting’s local zone?
Store the instant in a timestamptz column and the zone name (for example America/Sao_Paulo) in a separate text column. Convert with AT TIME ZONE when you need the local wall-clock time.
Should I store money as cents in a bigint?
That also works and is common in payment systems, as long as everyone remembers the unit. numeric(12,2) is simpler to read and avoids conversion mistakes.
Is jsonb slower than normal columns?
Reading a whole document is fast, but filtering on keys without an index is slower than on a real column, and the planner has fewer statistics. Put fields you filter, join or sort on in real columns.
What happens to existing rows when I add an enum column with a default?
Every existing row gets the default value, instantly, because a constant default is stored once in the catalog instead of in each row.