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

Alternative	Use When	Don't Use When
ORM query builder	Team standardizes on one stack	Complex SQL becomes opaque
Materialized view	Repeat expensive aggregates	Needs refresh strategy
Warehouse replica	Heavy BI scans	Not for OLTP latency

FAQs

Should I format SQL in application strings?

Use multiline template strings or SQL files; lint in CI.

When is DISTINCT enough?

When you need unique rows without aggregates.

Does ORDER BY hurt indexes?

Sort can use index order if query matches index columns.

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

Reviewed by Chris St. John·Last updated Jul 19, 2026

Was this article helpful?

Sql Data Types Jsonb Uuid Enum And Domain Types

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

Alternative	Use When	Don't Use When
ORM query builder	Team standardizes on one stack	Complex SQL becomes opaque
Materialized view	Repeat expensive aggregates	Needs refresh strategy
Warehouse replica	Heavy BI scans	Not for OLTP latency

FAQs

Should I format SQL in application strings?

Use multiline template strings or SQL files; lint in CI.

When is DISTINCT enough?

When you need unique rows without aggregates.

Does ORDER BY hurt indexes?

Sort can use index order if query matches index columns.

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

Reviewed by Chris St. John·Last updated Jul 19, 2026

Was this article helpful?

UUID, Enum & Domain Types

Search Documentation

UUID, Enum & Domain Types