UUID, Enum & Domain Types

When enums help and when lookup tables win. Practical PostgreSQL patterns for production queries.

Recipe

CREATE TYPE app.status AS ENUM ('pending','active','closed');
CREATE DOMAIN app.positive_int AS int CHECK (VALUE > 0);

When to reach for this: This pattern appears in application or reporting SQL you maintain.

Working Example

CREATE TABLE app.cases (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  status app.status NOT NULL DEFAULT 'pending',
  priority app.positive_int NOT NULL
);

What this demonstrates:

• Runnable SQL against sample schemas
• Idiomatic PostgreSQL syntax
• Patterns safe for EXPLAIN review

Deep Dive

How It Works

• PostgreSQL parses SQL into a query tree.
• The planner estimates costs using statistics.
• Executor returns rows to the client protocol.

Notes

• Prefer readable SQL; optimizers rewrite internally.
• Test with production-like row counts.

Gotchas

• SELECT * in apps - New columns break serializers.. Fix: List columns explicitly.
• NULL comparisons - = NULL is always unknown.. Fix: Use IS NULL or IS DISTINCT FROM.
• Implicit casts - Surprise seq scans on mismatched types.. Fix: Match predicate types to column types.
• Unbounded queries - Missing LIMIT on large tables.. Fix: Paginate or aggregate intentionally.
• Stale statistics - Bad plans after bulk load.. Fix: Run ANALYZE on affected tables.

Alternatives

FAQs

Related

• Data Types Basics - overview

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.