PostgreSQL Data Types:

There's a specific kind of bug that doesn't show up right away. A latitude column stored as FLOAT, and months later your distance calculations are just slightly off in a way that's hard to pin down. Or a price column using the same type, and a customer notices their total is a cent wrong, in a way that somehow compounds across enough transactions to matter. Nobody wrote a bug in the usual sense. The data type was the bug, and it had been sitting there quietly since the table was created.

Choosing a column's type feels like the most mechanical part of designing a schema — pick something that looks roughly right and move on. I used to treat it that way too. It's actually one of the few decisions that's genuinely annoying to undo later, because by the time you notice it's wrong, there's real data sitting in that column.

A table that works fine until it doesn't

Say you're building a venues table for a dashboard that tracks locations and closures. It's tempting to just reach for whatever feels familiar — VARCHAR for text, FLOAT for anything with a decimal, INTEGER for IDs without thinking twice.

That table inserts rows fine. It passes whatever tests you write in the first week. Then a risk score computed from FLOAT values starts drifting in a way that doesn't reproduce consistently, or the ID column runs into its ceiling because nobody considered BIGINT, or a TIMESTAMP column with no timezone awareness makes an event look like it happened in a different hour depending on which server handled it. None of these are exotic problems. They're just what happens when types don't get real thought up front.

Going through the types one at a time

The table itself

CREATE TABLE venues (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    latitude DOUBLE PRECISION NOT NULL,
    longitude DOUBLE PRECISION NOT NULL,
    is_closed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

This is a fairly realistic starting point for a venue-tracking dashboard. Every type choice here is worth understanding on its own, not just copying because it looked right in a tutorial.

INTEGER / BIGINT — good for counts and IDs. The distinction matters once you think about scale: INTEGER tops out around 2.1 billion. If there's any real chance a table could grow past that — a high-volume events table, say — starting with BIGINT costs nothing now and avoids a painful migration later.

SERIAL / BIGSERIAL — the usual auto-incrementing ID setup. It's worth knowing this isn't actually a separate type. It's an INTEGER (or BIGINT) column backed by a sequence that hands out the next value automatically. That distinction matters later if you ever need to work with the sequence directly.

VARCHAR(n) vs TEXT — this one surprised me coming from MySQL. In Postgres these perform almost identically. TEXT is usually the better default unless you actually need to enforce a maximum length — VARCHAR(n) is really just TEXT with a length constraint bolted on, not a different storage format underneath.

NUMERIC(p,s) vs DOUBLE PRECISION — this is the one that actually causes real incidents. NUMERIC stores exact decimal values. DOUBLE PRECISION is a floating-point approximation. Don't use FLOAT/DOUBLE PRECISION for money — the rounding errors aren't a hypothetical, they show up given enough transactions. DOUBLE PRECISION is fine for something like geospatial coordinates, where the imprecision at that scale doesn't really change the result.

BOOLEAN — flags. Not much to say here.

TIMESTAMP vs TIMESTAMPTZ — TIMESTAMPTZ stores the value normalized to UTC and converts it on display based on the client's timezone. TIMESTAMP just stores whatever you gave it, with no timezone awareness at all. For pretty much any real app — anything with users spread across timezones, or a backend and frontend that might not agree on local time — TIMESTAMPTZ is the safer default, even though TIMESTAMP looks simpler at first glance.

JSONB — binary-stored, indexable, faster to query than JSON, which is just stored as raw text. Anything semi-structured, like a payload synced from a third-party API, belongs in JSONB by default.

UUID — useful when IDs need to be generated somewhere other than the database itself, or across multiple systems, without collision risk. Needs either gen_random_uuid() or an extension like pgcrypto or uuid-ossp.

The two questions that always come up

"Difference between VARCHAR and TEXT?" — functionally almost identical in Postgres, unlike MySQL. VARCHAR(n) just adds a length constraint on top of the same underlying storage.

"Difference between JSON and JSONB?" — JSON stores the exact text, preserving whitespace and key order, but it's slower to query and doesn't support much indexing. JSONB stores a parsed binary format, which loses the exact original formatting but is faster and indexable through GIN indexes. Default to JSONB most of the time, and only reach for plain JSON if you specifically need to preserve the text exactly as it came in.

Altering a table isn't always as harmless as it looks

ALTER TABLE venues ADD COLUMN city VARCHAR(50);
ALTER TABLE venues DROP COLUMN city;
ALTER TABLE venues ALTER COLUMN name TYPE TEXT;
ALTER TABLE venues RENAME COLUMN name TO venue_name;

These look simple, and on a small table they are. The problem shows up at scale — altering a table with millions of rows during peak traffic can lock it, blocking reads and writes for however long the operation takes. ADD COLUMN with a default value used to force a full table rewrite in older Postgres versions. That got a lot cheaper starting in Postgres 11 for constant defaults, but it's worth knowing that history exists, since not every production system runs the latest version, and assuming modern behavior on an older cluster is a reasonable way to cause an outage.

DROP vs TRUNCATE vs DELETE

DROP TABLE venues;          -- removes the table and its structure entirely
TRUNCATE TABLE venues;      -- empties all rows, keeps the structure
DELETE FROM venues;         -- removes rows, can be filtered, slower, undoable within a transaction

DELETE works row by row, gets logged, supports WHERE, and is fully transaction-safe — you can roll it back. TRUNCATE is close to instant because it deallocates whole pages instead of scanning rows one at a time. It can't use WHERE, but it's still transactional in Postgres, so it can be rolled back too, as long as you're inside a transaction that hasn't committed. DROP removes the entire table, structure included, not just what's inside it.

Fixing a type before it becomes a real migration

Say the venues table above shipped with risk_score DOUBLE PRECISION, because someone figured "it's just a number between 0 and 1, a float is fine." A few weeks later, a report comparing risk scores across venues starts showing inconsistent sort order for values that should be identical — classic floating-point comparison drift.

Caught early, the fix is a simple alteration:

ALTER TABLE venues ALTER COLUMN risk_score TYPE NUMERIC(3,2);

Caught after the table has millions of rows, this becomes a much more careful operation — possibly needing a maintenance window, or a safer pattern like adding a new column, backfilling it, and swapping it in. The gap between fixing this on day one and fixing it six months later isn't small. It's the difference between a one-line change and a planned migration.

Where this leaves you

Types in Postgres aren't just about what values a column technically accepts — they carry real assumptions about precision, timezone handling, and scale. Those assumptions are cheap to get right when you're creating the table and expensive to fix once there's data in it. Next time you write a CREATE TABLE, it's worth treating each type as a real decision, especially NUMERIC vs DOUBLE PRECISION for anything money-related, and TIMESTAMPTZ vs TIMESTAMP for anything tied to a user.

Next up: actually working with the data — INSERT, SELECT, UPDATE, DELETE, and the upsert pattern for handling "insert or update" cleanly.