english/.opencode/skills/databases/stacks/postgres.md

PostgreSQL Rules

Guidelines for designing schemas specific to PostgreSQL.

---

Data Types

Use case	Type	Notes
Primary key	BIGSERIAL	Auto-increment BIGINT
Primary key (small)	SERIAL	Auto-increment INT
Foreign key	Match with PK type
Money/Price	NUMERIC(18,2)	Precise
Boolean	BOOLEAN	TRUE/FALSE/NULL
Short text	VARCHAR(n) or TEXT	TEXT has no limit
JSON	JSONB	Queryable, prefer over JSON
Timestamp	TIMESTAMPTZ	Always use with timezone
Date	DATE
UUID	UUID	Native type
Array	TEXT[], INT[]
IP Address	INET

TIMESTAMPTZ vs TIMESTAMP

IMPORTANT: Always use TIMESTAMPTZ to avoid timezone issues.

---

Comments (NO metadata tables needed)

PostgreSQL supports COMMENT ON:

Table comment

COMMENT ON TABLE orders IS 'Table storing customer orders';

Column comments

COMMENT ON COLUMN orders.id IS 'Primary key, auto increment';
COMMENT ON COLUMN orders.order_number IS 'Unique order code, format: ORD-YYYYMMDD-XXXXX';
COMMENT ON COLUMN orders.status IS 'Status: pending|confirmed|shipped|cancelled';

Query comments

-- Table comment
SELECT obj_description('orders'::regclass);

-- Column comments
SELECT
    c.column_name,
    pgd.description
FROM information_schema.columns c
LEFT JOIN pg_catalog.pg_statio_all_tables st
    ON c.table_schema = st.schemaname AND c.table_name = st.relname
LEFT JOIN pg_catalog.pg_description pgd
    ON pgd.objoid = st.relid AND pgd.objsubid = c.ordinal_position
WHERE c.table_name = 'orders';

---

Index Types

B-Tree (default)

CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_user_status_created ON orders(user_id, status, created_at DESC);

Partial Index (very useful)

-- Only index non-deleted records
CREATE INDEX idx_orders_active ON orders(user_id, status)
    WHERE deleted_at IS NULL;

-- Only index pending orders
CREATE INDEX idx_orders_pending ON orders(created_at)
    WHERE status = 'pending';

GIN (for JSONB, Arrays, Full-text)

CREATE INDEX idx_orders_metadata ON orders USING GIN(metadata);
CREATE INDEX idx_products_tags ON products USING GIN(tags);

-- Full-text search
CREATE INDEX idx_products_search ON products
    USING GIN(to_tsvector('english', name || ' ' || description));

BRIN (for large tables with ordered data)

CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

---

Sequences (Auto Increment)

-- SERIAL/BIGSERIAL auto-creates sequence
CREATE TABLE users (id BIGSERIAL PRIMARY KEY);

-- Identity columns (PostgreSQL 10+)
CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- Custom sequence
CREATE SEQUENCE order_number_seq START 1000;

---

Auto update timestamp (requires trigger)

PostgreSQL does not have ON UPDATE CURRENT_TIMESTAMP. Need to create trigger:

-- Function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to table
CREATE TRIGGER trg_orders_updated_at
    BEFORE UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

---

ENUM Type

-- Create enum type
CREATE TYPE order_status AS ENUM (
    'pending', 'confirmed', 'shipped', 'cancelled', 'completed'
);

-- Use in table
CREATE TABLE orders (
    status order_status NOT NULL DEFAULT 'pending'
);

-- Add new value
ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'completed';

Note: Cannot remove values from ENUM.

---

Partitioning

Range partitioning

CREATE TABLE fact_orders (
    id BIGSERIAL,
    order_date DATE NOT NULL,
    ...
) PARTITION BY RANGE (order_date);

CREATE TABLE fact_orders_2024 PARTITION OF fact_orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE fact_orders_default PARTITION OF fact_orders DEFAULT;

Hash partitioning

CREATE TABLE logs PARTITION BY HASH (user_id);
CREATE TABLE logs_0 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE logs_1 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 1);

---

Example DDL

-- Create enum
CREATE TYPE order_status AS ENUM ('pending', 'confirmed', 'shipped', 'cancelled');

-- Create table
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL,
    user_id BIGINT NOT NULL,
    status order_status NOT NULL DEFAULT 'pending',
    subtotal NUMERIC(18,2) NOT NULL DEFAULT 0,
    discount_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    total_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT uk_orders_number UNIQUE (order_number),
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Indexes
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_user_status_created ON orders(user_id, status, created_at DESC);
CREATE INDEX idx_orders_metadata ON orders USING GIN(metadata);

-- Comments
COMMENT ON TABLE orders IS 'Table storing order information';
COMMENT ON COLUMN orders.order_number IS 'Unique order code';
COMMENT ON COLUMN orders.status IS 'Order status';

-- Trigger for updated_at
CREATE TRIGGER trg_orders_updated_at
    BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

---

Checklist

• Use TIMESTAMPTZ instead of TIMESTAMP
• Use JSONB instead of JSON
• COMMENT ON for tables and columns
• Trigger for updated_at
• Partial indexes for soft delete / filtered queries
• GIN index for JSONB columns