postgresql

# PostgreSQL ## Data Type Defaults | Need | Use | Avoid | |------|-----|-------| | Primary key | `BIGINT GENERATED ALWAYS AS IDENTITY` | `SERIAL`, `BIGSERIAL` | | Timestamps | `TIMESTAMPTZ` | `TIMESTAMP` (loses timezone) | | Text | `TEXT` | `VARCHAR(n)` unless constraint needed | | Money | `NUMERIC(precision, scale)` | `MONEY`, `FLOAT` | | Boolean | `BOOLEAN` with `NOT NULL DEFAULT` | nullable booleans | | JSON | `JSONB` | `JSON` (no indexing), text JSON | | UUID | `gen_random_uuid()` (PG13+) | `uuid-ossp` extension | | IP addresses | `INET` / `CIDR` | text | | Ranges | `TSTZRANGE`, `INT4RANGE`, etc. | pair of columns | ## Schema Rules - Every FK column gets an index (PG does NOT auto-create these) - `NOT NULL` on every column unless NULL has business meaning - `CHECK` constraints for domain rules at DB level - `EXCLUDE` constraints for range overlaps: `EXCLUDE USING gist (room WITH =, during WITH &&)` - Default `created_at TIMESTAMPTZ NOT NULL DEFAULT now()` - Separate `updated_at` with trigger, never trust app layer alone. Gate it with `WHEN (OLD.* IS DISTINCT FROM NEW.*)` so a no-op write neither fires the function nor bumps the timestamp -- on `BEFORE UPDATE` the row image is built before the trigger runs, so the comparison sees the caller's row, not the one the trigger is about to stamp. - Use `BIGINT` PKs -- cheaper JOINs than UUID, better index locality - Safe migrations: `CREATE INDEX CONCURRENTLY`, add columns with a **non-volatile** `DEFAULT` (instant add). Neve...