PostgreSQL Features

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
    -- GENERATED ALWAYS (recommended)
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    -- GENERATED BY DEFAULT (allows manual inserts)
    legacy_id BIGINT GENERATED BY DEFAULT AS IDENTITY
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    quantity INT NOT NULL,
    unit_price NUMERIC(10,2) NOT NULL,
    -- Stored generated column
    total NUMERIC(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

-- Inline
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY
);

-- Named constraint
CREATE TABLE users (
    id BIGSERIAL,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

-- Composite primary key
CREATE TABLE order_items (
    order_id BIGINT,
    product_id BIGINT,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,

    -- Inline reference
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION
);

-- Single column
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

-- Composite unique
ALTER TABLE order_items ADD CONSTRAINT order_items_unique
    UNIQUE (order_id, product_id);

-- Partial unique (with index)
CREATE UNIQUE INDEX idx_users_active_email ON users(email)
    WHERE status = 'active';

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    price NUMERIC(10,2) NOT NULL,
    quantity INT NOT NULL,

    CONSTRAINT chk_price_positive CHECK (price > 0),
    CONSTRAINT chk_quantity_non_negative CHECK (quantity >= 0)
);

CREATE TABLE reservations (
    id BIGSERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    during TSTZRANGE NOT NULL,

    CONSTRAINT reservations_no_overlap
        EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    manager_id BIGINT,

    CONSTRAINT fk_manager
        FOREIGN KEY (manager_id)
        REFERENCES employees(id)
        DEFERRABLE INITIALLY DEFERRED
);

-- B-tree (default)
CREATE INDEX idx_users_email ON users(email);

-- Descending order
CREATE INDEX idx_orders_date ON orders(created_at DESC);

-- Multiple columns
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Hash (equality only)
CREATE INDEX idx_users_email_hash ON users USING hash(email);

-- GIN (arrays, JSONB, full-text)
CREATE INDEX idx_products_tags ON products USING gin(tags);
CREATE INDEX idx_users_data ON users USING gin(metadata jsonb_path_ops);

-- GiST (geometric, range, full-text)
CREATE INDEX idx_locations ON places USING gist(location);

-- BRIN (very large tables, sorted data)
CREATE INDEX idx_logs_time ON logs USING brin(created_at);

-- SP-GiST (non-balanced data)
CREATE INDEX idx_ips ON connections USING spgist(ip_address inet_ops);

-- Only index active users
CREATE INDEX idx_users_active ON users(email)
    WHERE status = 'active';

-- Only index unprocessed orders
CREATE INDEX idx_orders_pending ON orders(created_at)
    WHERE processed_at IS NULL;

-- Include columns for index-only scans
CREATE INDEX idx_orders_user ON orders(user_id)
    INCLUDE (status, total);

-- Index on lowercase email
CREATE INDEX idx_users_email_lower ON users(lower(email));

-- Index on JSONB field
CREATE INDEX idx_users_city ON users((data->>'city'));

-- Index on date part
CREATE INDEX idx_orders_month ON orders(date_trunc('month', created_at));

CREATE VIEW active_users AS
SELECT id, email, name, created_at
FROM users
WHERE status = 'active';

-- With check option (prevents invalid inserts)
CREATE VIEW premium_users AS
SELECT * FROM users WHERE plan = 'premium'
WITH CHECK OPTION;

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    date_trunc('month', created_at) AS month,
    count(*) AS order_count,
    sum(total) AS revenue
FROM orders
GROUP BY 1
WITH DATA;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW monthly_sales;

-- Concurrently (requires unique index)
CREATE UNIQUE INDEX idx_monthly_sales ON monthly_sales(month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_user_orders(p_user_id BIGINT)
RETURNS TABLE(order_id BIGINT, total NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT id, total FROM orders WHERE user_id = p_user_id;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION user_order_count(uid BIGINT)
RETURNS INT AS $$
    SELECT count(*)::INT FROM orders WHERE user_id = uid;
$$ LANGUAGE sql STABLE;

CREATE FUNCTION example()
RETURNS void AS $$
    -- function body
$$ LANGUAGE plpgsql
VOLATILE           -- Can modify data (default)
-- STABLE          -- Returns same result for same args in same statement
-- IMMUTABLE       -- Returns same result for same args always
STRICT             -- Returns NULL if any arg is NULL
SECURITY DEFINER   -- Runs with function owner's permissions
PARALLEL SAFE;     -- Can run in parallel query

-- Before insert/update
CREATE TRIGGER update_users_timestamp
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

-- After insert
CREATE TRIGGER audit_user_creation
    AFTER INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION log_user_created();

CREATE TRIGGER refresh_stats_after_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH STATEMENT
    EXECUTE FUNCTION refresh_order_stats();

CREATE TRIGGER notify_high_value_order
    AFTER INSERT ON orders
    FOR EACH ROW
    WHEN (NEW.total > 1000)
    EXECUTE FUNCTION notify_sales_team();

CREATE TYPE order_status AS ENUM (
    'pending',
    'confirmed',
    'shipped',
    'delivered',
    'cancelled'
);

-- Use in table
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending'
);

CREATE TYPE address AS (
    street VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    country VARCHAR(100),
    postal_code VARCHAR(20)
);

-- Use in table
CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    billing_address address,
    shipping_address address
);

CREATE DOMAIN email_address AS VARCHAR(255)
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE DOMAIN positive_int AS INTEGER
    CHECK (VALUE > 0);

-- Use in table
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email email_address NOT NULL,
    age positive_int
);

-- Common extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";     -- UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";      -- Cryptographic functions
CREATE EXTENSION IF NOT EXISTS "pg_trgm";       -- Trigram similarity
CREATE EXTENSION IF NOT EXISTS "btree_gin";     -- GIN for scalars
CREATE EXTENSION IF NOT EXISTS "btree_gist";    -- GiST for scalars
CREATE EXTENSION IF NOT EXISTS "hstore";        -- Key-value store
CREATE EXTENSION IF NOT EXISTS "citext";        -- Case-insensitive text
CREATE EXTENSION IF NOT EXISTS "postgis";       -- Geographic objects
CREATE EXTENSION IF NOT EXISTS "timescaledb";   -- Time-series

-- Standalone sequence
CREATE SEQUENCE order_number_seq
    START WITH 1000
    INCREMENT BY 1
    NO MAXVALUE
    CACHE 10;

-- Use in table
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    order_number BIGINT NOT NULL DEFAULT nextval('order_number_seq')
);

COMMENT ON TABLE users IS 'Application users with authentication information';
COMMENT ON COLUMN users.status IS 'Account status: active, suspended, or deleted';
COMMENT ON INDEX idx_users_email IS 'Index for email lookups during authentication';
COMMENT ON FUNCTION update_updated_at() IS 'Trigger function to update updated_at timestamp';