PostgreSQL
PostgreSQL-specific SQL conventions for advanced features, extensions, and dialect idioms
Details
Language / Topic
SQLCategory
framework
Compatible Frameworks
postgresql
Rules
balanced
- Use `TIMESTAMPTZ` (timestamp with time zone) instead of `TIMESTAMP` for all date-time columns — PostgreSQL stores `TIMESTAMPTZ` in UTC and converts on display.
- Use `JSONB` instead of `JSON` for semi-structured data — `JSONB` is stored as binary, supports GIN indexing, and has faster query operators.
- Use `RETURNING id, created_at` on `INSERT` and `UPDATE` statements to retrieve generated values without a separate `SELECT` round-trip.
- Use `ON CONFLICT (col) DO UPDATE SET col = EXCLUDED.col` (upsert) instead of separate `SELECT` + `INSERT`/`UPDATE` to avoid race conditions in concurrent writes.
- Use `pg_trgm` extension with GIN index (`CREATE INDEX ON table USING GIN (col gin_trgm_ops)`) for efficient substring and ILIKE searches at scale.
- Use `LISTEN`/`NOTIFY` for lightweight real-time event signaling between the database and application instead of polling tables for changes.
- Use `COPY FROM STDIN` or `\copy` for bulk data loading — it is orders of magnitude faster than row-by-row `INSERT` for large datasets.