PostgreSQL Basics #2 Data Types and Table Design: What to Store Things As

Time to explain the habits planted in chapter 1’s first table. Half of table design is choosing data types, and mistakes here are the most expensive to fix once a service has grown. This chapter walks through the choices you face daily — strings, numbers, timestamps, primary keys, constraints — together with the criteria for deciding.

Strings: text is all you need #

The first question people arriving from other databases ask is “should this be varchar(255)?” PostgreSQL’s answer is simple: use text. In PostgreSQL, text and varchar are internally the same thing, and a length-limited varchar(n) is not any faster. If a length limit is a business rule (say, nicknames up to 20 characters), express it as a CHECK constraint rather than a type — the intent is visible and changing it is easy.

name text NOT NULL CHECK (char_length(name) <= 20)

Numbers: money is always numeric #

For integers, bigint covers most cases (a cheap insurance policy against the classic accident of starting with int and blowing past 2.1 billion into a migration). The trap is decimals. float and double precision are binary floating point, so 0.1 + 0.2 is not exactly 0.3. Anything that must be exact — money, quantities, rates — must be numeric. It guarantees decimal exactness at the cost of slower arithmetic, but workloads where that difference matters are rare. Float’s place is where approximation is acceptable: scientific computation and coordinates.

Points in time: timestamptz is the standard #

Timestamp storage is the most important section of this chapter. PostgreSQL has timestamp (without time zone) and timestamptz (time zone aware), and the working default is timestamptz. Despite the name, timestamptz does not store a time zone. It converts input to UTC for storage and converts back to the session’s time zone on read. In other words, it stores an absolute point in time. Plain timestamp stores the wall-clock digits unconverted, so the moment servers and clients with different time zones mix, the “whose 9 o’clock is this 9 o’clock” problem begins.

-- Absolute points in time (when an event happened) are timestamptz
created_at timestamptz NOT NULL DEFAULT now()

The only exception is a “wall-clock value independent of time zone” (like a store’s 09:00 opening time). If you only need the date there is date, and durations have interval.

Primary keys: bigint IDENTITY by default, UUID when there’s a requirement #

PK strategy has two branches.

The default is bigint GENERATED ALWAYS AS IDENTITY. Small, fast, simple. UUID becomes a requirement in cases like distributed generation (you need the ID before it reaches the database) or preventing guessable externally exposed IDs. If you use UUID, the current best practice is time-ordered uuidv7, avoiding the way v4’s random inserts scatter the index — and PostgreSQL 18 ships a built-in uuidv7() function, no extension needed. Running an internal bigint PK for joins alongside an external UUID column is also a common real-world compromise.

Constraints: integrity lives in the database layer #

Application-side validation gets bypassed — by the admin console, by batch jobs, by the second service that connects later. The last line of defense is database constraints.

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     bigint NOT NULL REFERENCES users (id),
    status      text NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    amount_usd  numeric NOT NULL CHECK (amount_usd >= 0),
    created_at  timestamptz NOT NULL DEFAULT now()
);

• Make NOT NULL the default, relaxing it only when “may be absent” is the actual meaning. NULL drags in three-valued logic (comparisons that are neither true nor false but unknown), so the fewer NULLs the simpler everything is.
• CHECK makes the database enforce value rules, and REFERENCES (foreign keys) makes it enforce relationship integrity. Hesitating over foreign keys for performance reasons is unfounded at most scales, and the cleanup cost of orphaned records is far higher.
• For status values, text + CHECK as above is the sensible start. An enum type exists, but adding and removing values is operationally awkward, so reserve it for genuinely fixed domains. Arrays (text[]) are useful for “multi-values not quite worth a child table” (tags and the like), and when you need to search them, a GIN index (the same family as chapter 7’s JSONB) backs them up.

Summary #

• Strings default to text. Express length rules with CHECK, not varchar(n).
• Money and quantities are numeric. Float is only for where approximation is fine. Starting integers at bigint is cheap insurance.
• The default for points in time is timestamptz — absolute instants stored as UTC. Plain timestamp is the seed of time zone confusion.
• PKs default to bigint IDENTITY; with distributed-generation or non-guessability requirements, UUID (v7). PostgreSQL 18 has uuidv7() built in.
• The last line of integrity defense is database constraints. NOT NULL by default and generous use of CHECK and foreign keys is this course’s design stance.