Naming & Style Rules

Consistent SQL naming reduces cognitive load across migrations, ORMs, BI tools, and on-call grep sessions. Pick conventions once, document in an ADR, enforce in review.

Recipe

-- Preferred conventions (example team ADR)
CREATE SCHEMA app;
 
CREATE TABLE app.order (          -- singular table ADR choice
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES app.customer (id),
  placed_at timestamptz NOT NULL DEFAULT now(),
  status text NOT NULL CHECK (status IN ('open', 'shipped', 'cancelled'))
);
 
CREATE INDEX idx_order_customer_placed
  ON app.order (customer_id, placed_at DESC);

When to reach for this: New service bootstrap, linter setup (SQLFluff), or refactoring legacy camelCase schemas.

Working Example

-- Constraint naming
ALTER TABLE app.order
  ADD CONSTRAINT fk_order_customer
  FOREIGN KEY (customer_id) REFERENCES app.customer (id);
 
ALTER TABLE app.order
  ADD CONSTRAINT ck_order_status
  CHECK (status IN ('open', 'shipped', 'cancelled'));
 
-- View naming
CREATE VIEW app.v_order_summary AS
SELECT customer_id, count(*) AS order_count
FROM app.order
GROUP BY customer_id;

Style guide excerpt:

Deep Dive

Singular vs Plural Tables

Singular (order, customer):

• Aligns with ORM entity class Order
• FK reads naturally: order.customer_id

Plural (orders, customers):

• Matches spoken SQL ("select from orders")
• Popular in Rails ecosystems

Rule: Either is valid - never mix in one database. Existing legacy picks the ADR; greenfield teams choose and document.

snake_case Everywhere

-- Avoid
CREATE TABLE app.OrderItems (orderItemId bigint);
 
-- Prefer
CREATE TABLE app.order_item (order_item_id bigint);  -- if singular ADR

• PostgreSQL folds unquoted identifiers to lowercase - quoting "camelCase" is a permanent trap.
• snake_case works without quoted identifiers in all clients.

Timestamp and Money Columns

placed_at timestamptz NOT NULL   -- instants
amount_cents bigint NOT NULL     -- money as integer minor units
currency_code char(3) NOT NULL   -- ISO 4217

• Never timestamp without time zone for real-world events.
• amount_cents beats float for money.

Comment Documentation

COMMENT ON TABLE app.order IS 'Customer purchase header; tenant-scoped via RLS';
COMMENT ON COLUMN app.order.status IS 'open|shipped|cancelled; expand via migration';

• Comments surface in pgAdmin/DBeaver and \d+.
• Document enum-like CHECK values when not using PostgreSQL ENUM type.

Gotchas

• Quoted mixed-case identifiers - Require quotes forever in every query. Fix: Rename in expand/contract migration to snake_case.
• Reserved words as names - user, order need quoting or renaming (app_user, sales_order). Fix: Prefix or suffix reserved names.
• Abbreviation soup - cust_ord_ln saves typing, loses readability. Fix: Prefer full words under 30 chars.
• Inconsistent index names - orders_customer_id_idx vs idx_order_customer. Fix: One template in SQLFluff config.
• Schema public for everything - Collision and security risk. Fix: Application schema app with explicit search_path.

Alternatives

FAQs

Related

• Postgres Project Rules Checklist - rules 1-5
• Migrations Basics - SQL in git
• Client Encoding & Locale - collation choices

External Related Links

• PostgreSQL Tutorial (Official) - Official hands-on introduction to PostgreSQL and SQL.
• PostgreSQL Tutorials & Other Resources (Official List) - Curated list of recommended tutorials and external resources.
• PostgreSQL Exercises (PG Exercises) - Interactive SQL exercises with a realistic dataset.
• Practical SQL, 2nd Edition - Anthony DeBarros.
• Just Use Postgres! - Denis Magda.

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.