---
name: postgresql-conventions
source: https://app.decimal.ai/s/postgresql-conventions@1/SKILL.md
source_sha256: 70eda94b72ec
---

# 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.
2. **Every timestamp** → `TIMESTAMPTZ`, defaulted with `now()`.
   Never `TIMESTAMP` (without time zone) or `DATETIME`.
3. **Surrogate primary keys** → `BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY`.
   Never `SERIAL`/`BIGSERIAL`, never `INT AUTO_INCREMENT`, never a bare `INTEGER`.
4. **Unbounded free text** (body, description, notes) → `TEXT`.
   Never `VARCHAR(255)` or any arbitrary length cap chosen "to be safe."
5. **Money / exact decimals** → `NUMERIC(p,s)`.
   Never `FLOAT`/`REAL`/`DOUBLE PRECISION` (rounding) and never the `MONEY` type.

### Constrained value sets

6. **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.
7. **A reusable validated scalar** shared across columns/tables → `CREATE DOMAIN`
   (e.g. `CREATE DOMAIN positive_amount AS NUMERIC(12,2) CHECK (VALUE > 0)`).
8. **Any other value validation** → an explicit `CHECK` constraint on the table.

### Semi-structured data (JSONB)

9. **Semi-structured / nested blobs** → `JSONB`. Never `JSON` (no binary form, no
   indexing) and never `TEXT` holding serialized JSON.
10. **A JSONB column that gets filtered** → add `CREATE INDEX ... USING gin (col);`.
11. **Filtering JSONB** → the containment operator `@>`
    (`data @> '{"status":"shipped"}'`). Never `data->>'status' = 'shipped'`, which
    cannot use the GIN index.

### Arrays

12. **An array column that gets filtered** → index it `USING gin (col)`.
13. **Array membership / overlap** → `@>` (contains) or `&&` (overlaps),
    e.g. `categories @> ARRAY['electronics']`. Never `'electronics' = ANY(categories)`,
    which is a sequential scan.

### Functions & triggers

14. **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

15. **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.
16. **Password storage** → pgcrypto `crypt(pw, gen_salt('bf'))`; verify with
    `crypt(input, stored) = stored`. Never store plaintext or an app-side hash.
17. **Privileges** → granular `GRANT SELECT, INSERT, ... ON <table>`. Never
    `GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public`.
18. **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`