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).

1. 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).

2. 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).

3. 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.