Getting Started

Creating Tables

Design PostgreSQL tables with the right data types, constraints, and relationships.

PostgreSQL Data Types

PostgreSQL has a rich type system:

Numbers: SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION

Text: CHAR(n), VARCHAR(n), TEXT

Date/Time: DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL

Boolean: BOOLEAN

Identifiers: UUID, SERIAL, BIGSERIAL

JSON: JSON, JSONB (binary, indexed)

Arrays: INTEGER[], TEXT[]

Constraints

NOT NULL — column cannot be null
UNIQUE — all values must be unique
PRIMARY KEY — unique + not null
FOREIGN KEY — references another table
CHECK — custom validation
DEFAULT — default value

Example

sql

-- Create tables with proper types and constraints
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    uuid        UUID DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    username    VARCHAR(50) UNIQUE NOT NULL,
    first_name  VARCHAR(100),
    last_name   VARCHAR(100),
    age         INTEGER CHECK (age >= 0 AND age <= 150),
    role        VARCHAR(20) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
    is_active   BOOLEAN DEFAULT TRUE NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at  TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- Posts table with foreign key
CREATE TABLE posts (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title       VARCHAR(200) NOT NULL,
    slug        VARCHAR(200) UNIQUE NOT NULL,
    content     TEXT,
    tags        TEXT[],        -- array type
    metadata    JSONB,         -- binary JSON
    views       INTEGER DEFAULT 0,
    published   BOOLEAN DEFAULT FALSE,
    published_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Create indexes for performance
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_published ON posts(published) WHERE published = TRUE;
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);

-- Full text search index
CREATE INDEX idx_posts_fts ON posts
USING GIN(to_tsvector('english', title || ' ' || COALESCE(content, '')));

Want to run this code interactively?

Try in Compiler