postgres-coding

PostgreSQL PL/pgSQL Coding Standards

Naming Conventions

Element	Convention	Example
Table	snake_case, plural	orders, order_items
Column	snake_case, singular	first_name, created_at
Function	snake_case, verb_noun	get_order_by_id, calculate_total
Trigger	trg_<table>_<event>	trg_orders_before_insert
Index	idx_<table>_<column(s)>	idx_orders_customer_id
Primary Key	pk_<table>	pk_orders
Foreign Key	fk_<child>_<parent>	fk_order_items_orders
Enum Type	snake_case, singular	order_status, payment_method

Function Pattern

CREATE OR REPLACE FUNCTION get_order_by_id(p_order_id INTEGER)
RETURNS TABLE (
    id INTEGER,
    customer_name TEXT,
    total NUMERIC(18,2),
    created_at TIMESTAMPTZ
)
LANGUAGE plpgsql
STABLE
AS $function$
BEGIN
    RETURN QUERY
    SELECT o.id, o.customer_name, o.total, o.created_at
    FROM orders o
    WHERE o.id = p_order_id;
END;
$function$;

Trigger Pattern

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

CREATE TRIGGER trg_orders_before_update
    BEFORE UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION trg_set_modified_at();

Table Creation

CREATE TABLE IF NOT EXISTS orders (
    id             INTEGER        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name  TEXT           NOT NULL,
    total          NUMERIC(18,2) NOT NULL DEFAULT 0,
    status         order_status  NOT NULL DEFAULT 'pending',
    metadata       JSONB,
    created_at     TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    created_by     TEXT          NOT NULL,
    modified_at    TIMESTAMPTZ,
    modified_by    TEXT,
    CONSTRAINT ck_orders_total CHECK (total >= 0)
);

CREATE INDEX IF NOT EXISTS idx_orders_customer_id ON orders (customer_id);
CREATE INDEX IF NOT EXISTS idx_orders_metadata ON orders USING GIN (metadata);

JSONB Operations

-- Query JSONB field
SELECT * FROM orders WHERE metadata->>'priority' = 'high';

-- JSONB containment
SELECT * FROM orders WHERE metadata @> '{"tags": ["urgent"]}';

-- Update JSONB field
UPDATE orders SET metadata = metadata || '{"reviewed": true}' WHERE id = 1;

-- Index for JSONB queries
CREATE INDEX idx_orders_metadata_priority ON orders ((metadata->>'priority'));

Partitioning

CREATE TABLE events (
    id          INTEGER GENERATED ALWAYS AS IDENTITY,
    event_date  DATE NOT NULL,
    payload     JSONB
) PARTITION BY RANGE (event_date);

CREATE TABLE events_2026_q1 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

Row-Level Security

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.tenant_id')::INTEGER);

CTEs and Window Functions

WITH monthly_totals AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', created_at) AS month,
        SUM(total) AS monthly_total
    FROM orders
    GROUP BY customer_id, DATE_TRUNC('month', created_at)
)
SELECT
    customer_id,
    month,
    monthly_total,
    LAG(monthly_total) OVER (PARTITION BY customer_id ORDER BY month) AS prev_month
FROM monthly_totals;