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.