Postgres – Tables, Keys and Constraints

A constraint is a rule the database will not let you break, no matter which application, script or person is connected. Everything you enforce in application code is a rule that holds until someone writes a second application, runs a fix-up script, or edits a row by hand at 3am.

This post builds a table properly, then works through the constraints in the order you should reach for them.

CREATE TABLE

CREATE TABLE payouts (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    host_id     integer     NOT NULL REFERENCES users(id),
    amount      numeric(10,2) NOT NULL CHECK (amount > 0),
    currency    char(3)     NOT NULL DEFAULT 'USD',
    paid_at     timestamptz,
    created_at  timestamptz NOT NULL DEFAULT now()
);

Six columns, five rules. Nothing in there is optional decoration — each one removes a class of row that would otherwise reach production.

Identity, not serial

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY   -- do this
id bigserial PRIMARY KEY                             -- not this

serial is not a type. It is shorthand that creates an integer column, a sequence, and a default — and then the sequence is a separate object with its own ownership and its own permissions, which is why permission denied for sequence is such a common first error after a careful GRANT.

GENERATED ALWAYS AS IDENTITY is the standard SQL replacement and it is stricter in the way you want: an INSERT that supplies its own id is rejected rather than quietly desynchronising the sequence. When you genuinely need to supply one — restoring a backup — OVERRIDING SYSTEM VALUE says so out loud.

NOT NULL and DEFAULT

The cheapest constraint there is, and the most under-used. Before adding a nullable column, ask what a NULL there would mean. If the answer is "nothing, it just wasn't filled in yet", the column wants NOT NULL DEFAULT:

created_at timestamptz NOT NULL DEFAULT now()

now() is evaluated per row at insert time, not frozen when the table was created — it is stored as an expression. Note that now() returns the time the transaction started, so every row written in one transaction shares a timestamp. That is usually what you want; clock_timestamp() is the one that moves.

Primary and foreign keys

host_id integer NOT NULL REFERENCES users(id)

That guarantees every host_id matches a real user, and it costs you a lookup on every insert. The part worth deciding deliberately is what happens when the user is deleted:

The booking schema uses CASCADE from properties to their images, and leaves bookings on RESTRICT. Deleting a user with bookings should fail loudly, because the answer is almost always that the user should be marked deleted rather than removed.

Postgres does not index a foreign key column for you. The parent's key is indexed because it is a primary key; the child's is not. Without one, deleting a parent scans the whole child table to check for references — which is how a delete on a small table takes thirty seconds.

UNIQUE

ALTER TABLE payouts ADD CONSTRAINT uq_payouts_host_day UNIQUE (host_id, paid_at);

A unique constraint is implemented as a unique index, so it costs the same and gives you the lookup for free. One thing surprises people: NULLs do not conflict. Two rows with the same host_id and a NULL paid_at are both allowed, because the two NULLs are not equal. Postgres 15 added UNIQUE NULLS NOT DISTINCT for when you want the other behaviour.

CHECK

CHECK (amount > 0)

Any expression over the columns of a single row. The booking table uses one to state something that is otherwise only true by convention:

-- ERROR:  new row for relation "bookings" violates check constraint
--         "ck_bookings_checkout_after_checkin"
INSERT INTO bookings (property_id, guest_id, check_in, check_out, guests, nights, nightly_rate,
                      subtotal, cleaning_fee, service_fee, total, status, public_id)
VALUES (1, 1, DATE '2026-01-10', DATE '2026-01-05', 2, 1, 100, 100, 10, 12, 122, 'PENDING',
        gen_random_uuid());

Name your constraints. Postgres will generate a name if you do not, and the generated one is what your application sees in the error — so the difference between a helpful message and violates check constraint "bookings_check1" is one clause.

The one that does real work

Two guests must not book the same property on overlapping nights. That is not a uniqueness rule — the dates differ — and it is the kind of thing usually left to the application: read the calendar, decide it is free, insert. Between the read and the insert, someone else books it.

An exclusion constraint states it directly:

CREATE EXTENSION IF NOT EXISTS btree_gist;

-- The booking table already carries this one, so drop it before re-creating it if
-- you are following along against the sample database.
ALTER TABLE bookings DROP CONSTRAINT IF EXISTS no_overlapping_bookings;

ALTER TABLE bookings ADD CONSTRAINT no_overlapping_bookings
    EXCLUDE USING gist (
        property_id                          WITH =,
        daterange(check_in, check_out, '[)') WITH &&
    )
    WHERE (status IN ('PENDING', 'CONFIRMED', 'COMPLETED'));

Read it as: no two rows may have the same property_id and overlapping date ranges — considering only rows whose status blocks the calendar, so a cancelled booking frees its nights. btree_gist is what lets an integer equality and a range overlap live in one GiST index.

Trying to double-book is now impossible rather than unlikely:

ERROR:  conflicting key value violates exclusion constraint "no_overlapping_bookings"
DETAIL:  Key (property_id, daterange(check_in, check_out, '[)'))=(100, [2024-04-10,2024-04-15))
         conflicts with existing key (property_id, daterange(check_in, check_out, '[)'))
         =(100, [2024-04-10,2024-04-15)).

No lock taken by the application, no race to lose, and it holds against every client — the API, a migration, a person in psql. The application still checks availability first, because a clean "those dates are taken" beats a database error, but correctness no longer depends on that check winning the race.

'[)' is the range's bounds: inclusive start, exclusive end. It is the reason a checkout on the 15th does not collide with a check-in on the 15th, which is exactly how hotels work.

Generated columns

A column computed from the others, stored and maintained by Postgres. Useful when a derived value is queried often enough that recomputing it in every query is wasteful — and better than a column the application has to remember to update:

ALTER TABLE bookings
    ADD COLUMN stay daterange
    GENERATED ALWAYS AS (daterange(check_in, check_out, '[)')) STORED;

You cannot write to it, and it cannot reference another table or anything non-deterministic — no now(). That restriction is the point: the value is always consistent with the row, because there is no path by which it could not be.

Adding a constraint to a table that already has rows

ALTER TABLE bookings ADD CONSTRAINT ck_guests_positive CHECK (guests > 0) NOT VALID;
ALTER TABLE bookings VALIDATE CONSTRAINT ck_guests_positive;

NOT VALID applies the rule to new and changed rows immediately without reading the existing ones, so it takes a brief lock rather than a long one. VALIDATE then checks the rest with a weaker lock that does not block writes. On a large table this is the difference between a deploy and an outage, and the migrations post returns to it.