iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

CREATE TABLE

By · Reviewed & last updated · Part of PostgreSQL Tutorial

A table is a typed, constrained store of rows. Put as much correctness as possible into the schema: types, defaults, NOT NULL, CHECK, UNIQUE, FOREIGN KEY. Indexes follow your queries, not your guesses.

A real users table

EXAMPLE
CREATE TABLE users (
    id          BIGSERIAL    PRIMARY KEY,
    email       TEXT         NOT NULL UNIQUE,
    name        TEXT         NOT NULL,
    role        TEXT         NOT NULL DEFAULT 'member'
                                CHECK (role IN ('admin','editor','member')),
    age         INT          CHECK (age >= 0 AND age < 150),
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ
);

CREATE TABLE posts (
    id        BIGSERIAL PRIMARY KEY,
    user_id   BIGINT    NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title     TEXT      NOT NULL,
    body      TEXT,
    published BOOLEAN   NOT NULL DEFAULT false,
    UNIQUE (user_id, title)
);

-- Useful index on the FK + a partial index on published
CREATE INDEX posts_user_idx ON posts(user_id);
CREATE INDEX posts_published_idx ON posts(created_at) WHERE published = true;

Why it matters

Constraints catch bugs no test ever will. The schema is the cheapest, most reliable place to enforce invariants — use it.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
CREATE TABLE users (
    id    BIGSERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    name  TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);
Try it Yourself »

Discussion

Loading…