PostgreSQL Data Types, Defaults, and Constraints
A PostgreSQL type defines how a value is represented and which operations make sense; a constraint defines which typed values are valid for the business. Good schemas use the narrowest meaningful semantics—not the smallest storage at any cost—and protect durable rules with nullability, checks, uniqueness, keys, and deliberate defaults.
What will you be able to do?
- Choose among text, numeric, boolean, temporal, UUID, array, JSONB, and binary types.
- Explain NULL, defaults, domains, identity columns, and generated columns.
- Match each constraint to a business invariant.
- Detect lossy or ambiguous type choices.
Prerequisites
The four foundation tables must exist. Use a transaction and roll it back for experiments.
Mental model
A type answers “what kind of value is this?” A constraint answers “which values or relationships are permitted?” A default answers “what value is supplied when the writer omits one?” None of these answers “what did the user intend?”—that still requires domain design.
How do you choose important types?
PostgreSQL timestamptz stores an instant and displays it in the session time zone; it does not preserve the originally typed zone name.
How do constraints express a domain?
Add a service-level target and an exact monetary credit:
The currency pattern checks shape, not whether a code is officially assigned. A reference table can enforce an approved set when that is the actual rule.
NOT NULL means absence is invalid. A default does not imply not-null: a writer can explicitly send NULL unless a constraint rejects it. UNIQUE generally permits multiple nulls unless configured with the relevant PostgreSQL null-distinct behavior.
When should you use domains, enums, and JSONB?
- A domain reuses a scalar type plus checks, but schema evolution and error clarity need evaluation.
- An enum strongly names a stable ordered set, but adding/reworking lifecycle states can complicate deployments.
- A lookup table supports metadata and change but adds joins and governance.
- jsonb works for genuinely variable, nested payloads; extract frequently queried, constrained, joined, or permission-sensitive facts into columns/tables.
Why does this matter in AI-native systems?
Structured model output becomes untrusted typed input. A JSON schema may validate the response before SQL, but database types and constraints protect every writer and race. Store model identifiers, prompt versions, token counts, latency, and costs using explicit units; vague numeric fields make evaluation and billing analysis unreliable.
Prove it worked
In a rollbackable transaction, try a negative credit, an invalid currency shape, and a due time earlier than creation. Record the exact constraint name for each rejection. Then insert valid boundary values and query pg_typeof():
Production judgment
- Changing a type may rewrite a table or take locks; measure and plan migrations.
- Exactness, ranges, time zones, collation, and case rules are product decisions.
- Generated identity is not an authorization secret.
- Check constraints should be immutable for existing rows; cross-row rules need other designs.
- JSONB flexibility shifts validation, indexing, and evolution work rather than removing it.
Common mistakes
- Rounded money: floating point used for exact currency → use numeric or integer minor units with explicit currency.
- Wrong-day reports: local timestamps treated as instants → store instants with timestamptz and retain business zone where needed.
- Default still allows missing value: no NOT NULL → add nullability explicitly.
- JSONB queries become fragile: stable fields buried in payload → promote governed fields to relational columns.
AI Pair-Programmer Prompt
Hands-on exercise
Model an AI reply review with review_state, confidence, reviewed_at, and reviewed_by. Enforce confidence in [0,1] and consistent pending/reviewed timestamps.
Acceptance criterion: valid pending and approved rows work; values below 0, above 1, and inconsistent review states fail with named constraints.
Knowledge check
- Does a default reject explicit NULL?
- Why is timestamptz appropriate for created_at?
- When is JSONB a warning sign?
- Why is double precision risky for exact money?
Answers
- No; use NOT NULL when absence is invalid.
- It represents a global instant and converts display to the session time zone.
- When stable queried, joined, constrained, or permission-sensitive facts are hidden inside it.
- Binary floating point cannot represent many decimal values exactly.
Completion checklist
Primary references