PostgreSQL conventions

Contract

Enforces PostgreSQL-native types, operators, and features over the generic ANSI/MySQL-style defaults a model reaches for by habit. Apply to every piece of PostgreSQL DDL, query, function, or migration — the moment the target is Postgres, the native form is the required form even when the generic form would "work."

Rules

Types

1. Case-insensitive text (email, username, slug, handle) → CITEXT.

Never VARCHAR(n) or TEXT paired with manual LOWER() on writes/reads.

1. Every timestamp → TIMESTAMPTZ, defaulted with now().

Never TIMESTAMP (without time zone) or DATETIME.

1. Surrogate primary keys → BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY.

Never SERIAL/BIGSERIAL, never INT AUTO_INCREMENT, never a bare INTEGER.

1. Unbounded free text (body, description, notes) → TEXT.

Never VARCHAR(255) or any arbitrary length cap chosen "to be safe."

1. Money / exact decimals → NUMERIC(p,s).

Never FLOAT/REAL/DOUBLE PRECISION (rounding) and never the MONEY type.

Constrained value sets

1. A column with a fixed, closed set of values (status, role, currency, kind,

priority, environment) → CREATE TYPE x AS ENUM (...) and use type x. Never VARCHAR, with or without a CHECK (col IN (...)) list.

1. A reusable validated scalar shared across columns/tables → CREATE DOMAIN

(e.g. CREATE DOMAIN positive_amount AS NUMERIC(12,2) CHECK (VALUE > 0)).

1. Any other value validation → an explicit CHECK constraint on the table.

Semi-structured data (JSONB)

1. Semi-structured / nested blobs → JSONB. Never JSON (no binary form, no

indexing) and never TEXT holding serialized JSON.

1. A JSONB column that gets filtered → add CREATE INDEX ... USING gin (col);.
2. Filtering JSONB → the containment operator @>

(data @> '{"status":"shipped"}'). Never data->>'status' = 'shipped', which cannot use the GIN index.

Arrays

1. An array column that gets filtered → index it USING gin (col).
2. Array membership / overlap → @> (contains) or && (overlaps),

e.g. categories @> ARRAY['electronics']. Never 'electronics' = ANY(categories), which is a sequential scan.

Functions & triggers

1. A "touch updated_at" trigger → set NEW.updated_at = now() (or

CURRENT_TIMESTAMP) in a plpgsql function and gate the trigger with WHEN (OLD.* IS DISTINCT FROM NEW.*) so it fires only on real changes.

Security

1. Per-tenant / per-user row isolation → ALTER TABLE t ENABLE ROW LEVEL

SECURITY; then CREATE POLICY ... USING (...). Do not rely on an application WHERE clause as the only guard.

1. Password storage → pgcrypto crypt(pw, gen_salt('bf')); verify with

crypt(input, stored) = stored. Never store plaintext or an app-side hash.

1. Privileges → granular GRANT SELECT, INSERT, ... ON <table>. Never

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public.

1. Enabling an extension → CREATE EXTENSION IF NOT EXISTS "pgcrypto"; —

always the idempotent form.

Worked examples

Each pair is BEFORE (the generic default a model emits) → AFTER (conforming).

Rule 1 — case-insensitive text

sql
-- BEFORE
email VARCHAR(255) UNIQUE,           -- then LOWER(email) everywhere
-- AFTER
email CITEXT UNIQUE NOT NULL,        -- uniqueness & comparison are case-insensitive

Rule 2 — timestamps

sql
-- BEFORE
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- AFTER
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

Rule 3 — primary keys

sql
-- BEFORE
id SERIAL PRIMARY KEY,
-- AFTER
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

Rule 4 — unbounded text

sql
-- BEFORE
body VARCHAR(255),                   -- truncates a long article
-- AFTER
body TEXT NOT NULL,

Rule 5 — money

sql
-- BEFORE
amount FLOAT,                        -- 0.1 + 0.2 != 0.3
-- AFTER
amount NUMERIC(12,2) NOT NULL,

Rule 6 — fixed value set → ENUM

sql
-- BEFORE
status VARCHAR(20) CHECK (status IN ('pending','shipped','delivered')),
-- AFTER
CREATE TYPE order_status AS ENUM ('pending','shipped','delivered');
status order_status NOT NULL DEFAULT 'pending',

Rule 7 — reusable validated scalar → DOMAIN

sql
-- BEFORE
price NUMERIC(12,2) CHECK (price > 0),  -- repeated on every money column
-- AFTER
CREATE DOMAIN positive_amount AS NUMERIC(12,2) CHECK (VALUE > 0);
price positive_amount NOT NULL,

Rules 9–11 — JSONB

sql
-- BEFORE
metadata JSON,                                   -- or TEXT
SELECT * FROM orders WHERE data->>'status' = 'shipped';
-- AFTER
metadata JSONB NOT NULL DEFAULT '{}',
CREATE INDEX idx_orders_data ON orders USING gin (data);
SELECT * FROM orders WHERE data @> '{"status":"shipped"}';

Rules 12–13 — arrays

sql
-- BEFORE
SELECT * FROM products WHERE 'electronics' = ANY(categories);
-- AFTER
CREATE INDEX idx_products_categories ON products USING gin (categories);
SELECT * FROM products WHERE categories @> ARRAY['electronics'];

Rule 14 — touch trigger

sql
-- BEFORE
CREATE TRIGGER t BEFORE UPDATE ON tbl FOR EACH ROW EXECUTE FUNCTION touch();
-- AFTER
CREATE TRIGGER t BEFORE UPDATE ON tbl FOR EACH ROW
  WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE FUNCTION touch();

Rule 15 — row-level security

sql
-- BEFORE  (application code only: WHERE tenant_id = $1)
-- AFTER
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

Rule 16 — passwords

sql
-- BEFORE
INSERT INTO users (pw) VALUES ('hunter2');
-- AFTER
INSERT INTO users (pw_hash) VALUES (crypt('hunter2', gen_salt('bf')));

Rule 18 — extensions

sql
-- BEFORE
CREATE EXTENSION pgcrypto;                       -- errors on re-run
-- AFTER
CREATE EXTENSION IF NOT EXISTS pgcrypto;

Edge cases & exceptions

• VARCHAR(n) is right only when n is a true domain limit — a 2-char ISO

country code, a fixed-length license plate. For "a name" or "a description," there is no real limit → TEXT.

• ENUM vs lookup table: ENUM for a small, stable set the app branches on

(status). If values are user-managed or carry extra attributes (a currencies table with symbol + decimal places), a referenced lookup table is correct — but the closed sets in these tasks are ENUM.

• Adding an ENUM value is ALTER TYPE ... ADD VALUE (cannot run inside a

transaction block before PG12); that cost is expected, not a reason to fall back to VARCHAR.

• @> needs a top-level key/value match. To test nested or existence-only

keys use ?/#>; @> still covers {"status":"shipped"}-style equality, which is the common filter.

• gen_random_uuid() (pgcrypto / built-in in PG13+) is the UUID generator;

uuid-ossp's uuid_generate_v4() is the older equivalent. Either is fine; a surrogate sequence key still uses rule 3.

• identity vs serial: GENERATED ALWAYS AS IDENTITY is SQL-standard and

blocks accidental manual inserts into the key; prefer it. BIGSERIAL is the legacy form and still violates rule 3.

Do / Don't

• Do type case-insensitive columns CITEXT. Don't store VARCHAR and

sprinkle LOWER() at every call site.

• Do use TIMESTAMPTZ always. Don't use TIMESTAMP "because the app is

one time zone."

• Do filter JSONB and arrays with @>. Don't use ->>'k' = 'v' or

'v' = ANY(col) — both defeat the GIN index.

• Do model closed value sets as ENUM types. Don't approximate them with

VARCHAR + a CHECK (... IN ...) list.

• Do add a GIN index whenever a JSONB/array column is filtered. Don't leave

it unindexed or reach for a B-tree.

• Do enforce tenant isolation with RLS in the database. Don't trust the

application WHERE clause as the only boundary.

• Do hash passwords with crypt(...gen_salt('bf')). Don't store plaintext

or hash in application code.

Common mistakes

• Emitting SERIAL/BIGSERIAL for keys — the MySQL-shaped reflex; rule 3 wants

GENERATED ALWAYS AS IDENTITY.

• TIMESTAMP instead of TIMESTAMPTZ — silently drops the offset.
• VARCHAR(255) as a default string length — a MySQL artifact with no Postgres

meaning.

• JSON instead of JSONB, then querying with ->> — no index, slow scans.
• 'x' = ANY(arr) for array membership — looks idiomatic, but it is a sequential

scan and cannot use a GIN index; arr @> ARRAY['x'] can.

• A VARCHAR ... CHECK (col IN (...)) column where an ENUM type is the convention.
• FLOAT for money — introduces rounding error in totals.

Quick checklist

• ] Case-insensitive text → CITEXT
• ] Timestamps → TIMESTAMPTZ DEFAULT now()
• ] Keys → BIGINT GENERATED ALWAYS AS IDENTITY
• ] Long strings → TEXT; money → NUMERIC
• ] Closed value sets → CREATE TYPE ... AS ENUM; reusable scalar → CREATE DOMAIN
• ] Blobs → JSONB + GIN index, filtered with @>
• ] Arrays → GIN index, filtered with @>/&&
• ] Tenant isolation → RLS + CREATE POLICY; passwords → crypt(gen_salt('bf'))
• ] Extensions → CREATE EXTENSION IF NOT EXISTS