PostgreSQL Core & Schema
When to use this skill
-
Creating or modifying PostgreSQL tables (DDL).
-
Working with JSONB, Arrays, or specialized Postgres types.
-
Creating triggers or functions (PL/pgSQL).
- Data Types
-
Timestamps: Always use timestamptz (Timestamp with Time Zone), rarely timestamp (without TZ).
-
Text: Use text instead of varchar(n) unless a strict limit is architecturally required.
-
JSON: Use jsonb (binary) for storage and indexing, not json .
-
Primary Keys: bigint GENERATED ALWAYS AS IDENTITY or uuid (v4/v7).
- Constraints & Integrity
-
Check Constraints: Use CHECK constraints generously (e.g., CHECK (price > 0) ).
-
Foreign Keys: Index all FK columns manually (Postgres does not auto-index them).
-
Exclusion Constraints: Use where UNIQUE is not enough (e.g., non-overlapping time ranges).
- Advanced Features
-
Triggers: Use for audit logs or complex data consistency that cannot be enforced by constraints.
-
Partitions: Consider declarative partitioning for massive time-series tables.
-
Enumerations: Use Native Enums for strict, infrequently changing sets; otherwise use a reference table.