Creating Tables — Data Types, Constraints and Defaults

A table’s definition is its schema — the list of columns, their data types, and the constraints that govern what values are valid. Choosing the right data type for each column is one of the most impactful database design decisions: the right type enables efficient storage, indexing, and querying; the wrong type wastes space, requires awkward casting, or silently allows invalid data. PostgreSQL has a rich type system — far beyond the standard integer, varchar, and boolean — that includes native support for JSON documents, arrays, date/time arithmetic, and UUIDs. Choosing well here pays dividends throughout the lifetime of the application.

CREATE TABLE Syntax #

CREATE TABLE table_name (
    column_name  data_type  [constraints],
    ...
    [table_constraints]
);

-- Drop and recreate (development only — destroys all data)
DROP TABLE IF EXISTS posts;
CREATE TABLE posts ( ... );

-- Add a column to an existing table
ALTER TABLE posts ADD COLUMN excerpt TEXT;

-- Rename a column
ALTER TABLE posts RENAME COLUMN excerpt TO summary;

-- Drop a column
ALTER TABLE posts DROP COLUMN summary;

-- Change a column's type
ALTER TABLE posts ALTER COLUMN title TYPE VARCHAR(500);

PostgreSQL Data Types Reference #

Constraints #

CREATE TABLE users (
    id         BIGSERIAL    PRIMARY KEY,             -- unique, not null, auto-inc
    email      TEXT         NOT NULL UNIQUE,          -- must be present, no duplicates
    name       VARCHAR(100) NOT NULL,                 -- must be present
    age        INTEGER      CHECK (age >= 0 AND age <= 150),  -- value range
    role       TEXT         NOT NULL DEFAULT 'user'
               CHECK (role IN ('user', 'editor', 'admin')),   -- allowed values
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()    -- defaults to current time
);

-- Named constraints (easier to identify in error messages)
CREATE TABLE posts (
    id        BIGSERIAL PRIMARY KEY,
    title     TEXT      NOT NULL,
    author_id BIGINT    NOT NULL,
    status    TEXT      NOT NULL DEFAULT 'draft',

    CONSTRAINT posts_title_not_empty CHECK (LENGTH(TRIM(title)) > 0),
    CONSTRAINT posts_status_valid    CHECK (status IN ('draft', 'published', 'archived')),
    CONSTRAINT posts_author_fk       FOREIGN KEY (author_id) REFERENCES users(id)
);

-- Multi-column unique constraint (e.g. no duplicate slug per author)
CREATE TABLE posts (
    ...
    author_id BIGINT NOT NULL,
    slug      TEXT   NOT NULL,
    UNIQUE (author_id, slug)   -- this pair must be unique, each individually may repeat
);

Common Mistakes #

Mistake 1 — Using FLOAT for monetary values #

❌ Wrong — floating point rounding errors:

price FLOAT   -- 0.1 + 0.2 = 0.30000000000000004

✅ Correct — use NUMERIC for exact arithmetic:

price NUMERIC(12, 2)   -- exact: 9999999999.99 max ✓

Mistake 2 — Using TIMESTAMP instead of TIMESTAMPTZ #

❌ Wrong — timezone-naive, problematic across environments:

created_at TIMESTAMP DEFAULT NOW()   -- local time, no timezone

✅ Correct:

created_at TIMESTAMPTZ DEFAULT NOW()   -- stored as UTC ✓

Mistake 3 — Using JSON instead of JSONB #

❌ Wrong — JSON stores text, cannot be indexed:

metadata JSON   -- text storage, no indexing support

✅ Correct — JSONB is binary, indexable, and faster to query:

metadata JSONB   -- binary, indexed, faster ✓

Quick Reference #