Data Types and Domain Modeling

PostgreSQL data types are not just storage labels. They are part of the domain model: the database’s description of what values are possible, how values compare, how they are indexed, and which invalid states can never be stored. A column declared as text accepts almost anything textual. A column declared as citext, numeric(12,2), tstzrange, or a custom DOMAIN carries much more meaning.

The outcome of this lesson is practical: given a business rule, you should be able to decide whether it belongs in a built-in type, a domain, a check constraint, a foreign key, an enum, a lookup table, a range type, or application code. In the relational design part of this PostgreSQL course, that decision matters because schema shape determines which data anomalies PostgreSQL can prevent without trusting every application path.

How PostgreSQL Uses Types Internally

Every PostgreSQL value has a type identified in the system catalogs. The type tells the parser how to interpret literals, tells the executor which operators and functions are legal, tells indexes which operator classes can be used, and tells the storage layer how the value is represented. For example, integer has numeric comparison operators, text has collation-aware ordering, jsonb has containment operators, and tstzrange has overlap and adjacency operators.

Types are resolved early. When PostgreSQL sees an expression such as price * quantity, it must choose an operator implementation whose argument types match or can be reached through casts. That is why schema choices affect both correctness and performance. Storing money as text forces repeated conversion and permits malformed values. Storing it as numeric(12,2) makes scale, comparison, sorting, arithmetic, and invalid input handling part of the database contract.

PostgreSQL also distinguishes base types, composite types, array types, enum types, range types, and domains. A domain is a reusable constraint wrapper around an underlying type. If several tables need an email address with the same normalization and validation rule, a domain lets you declare that once and reuse it. The domain does not create a new storage format; it adds checks around assignment and casting into that domain.

Syntax Anatomy

A column definition combines a name, a type, nullability, defaults, constraints, and sometimes generated behavior. These parts answer different questions. The type answers what kind of value this is. NOT NULL answers whether absence is legal. A CHECK answers which values inside the type are legal for this table. A foreign key answers whether the value must correspond to another row. A unique constraint answers whether duplicates are legal.

CREATE DOMAIN email_address AS text
    CHECK (VALUE ~ '^[^@[:space:]]+@[^@[:space:]]+[.][^@[:space:]]+$');

CREATE TABLE customers (
    customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email email_address NOT NULL UNIQUE,
    display_name text NOT NULL CHECK (char_length(display_name) BETWEEN 2 AND 80),
    marketing_opt_in boolean NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now()
);

This model says that a customer must have one valid-looking email address, a bounded display name, a boolean marketing preference, and a creation timestamp with time zone. The database will reject an empty name, a missing email, a duplicate email, or an email that does not satisfy the domain check. The regular expression is deliberately modest; deep email validation belongs outside the database because deliverability and mailbox existence are external facts.

Example 1: Replacing Strings With Meaningful Types

Start with a common weak model: order totals and states stored as strings. It looks flexible, but it allows values such as 'ten dollars', 'PAID ', or 'refund maybe'. Those values force every query and every application path to rediscover the business rules.

CREATE TABLE orders_example_one (
    order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_status text NOT NULL CHECK (order_status IN ('draft', 'placed', 'paid', 'cancelled')),
    order_total numeric(12,2) NOT NULL CHECK (order_total >= 0),
    placed_at timestamptz
);

INSERT INTO orders_example_one (order_status, order_total)
VALUES ('draft', 0.00);

INSERT INTO orders_example_one (order_status, order_total)
VALUES ('paid', 19.95)
RETURNING order_id, order_status, order_total;

The expected returned row has status paid and total 19.95. An attempted insert with order_total below zero fails with a check-constraint violation. An attempted insert with order_status equal to 'Paid' also fails because the stored vocabulary is intentionally exact.

The design choice here is between text plus a CHECK, an enum, and a status lookup table. A check constraint is simple and easy to alter for a small vocabulary. An enum gives a named type and compact representation, but changing enum labels needs deliberate migration planning. A lookup table is best when statuses have their own metadata, display order, permissions, or lifecycle rules.

Example 2: Domains For Reused Invariants

Suppose invoices, refunds, and account credits all store currency amounts. Repeating numeric(12,2) CHECK (...) across many tables invites drift. A domain gives the invariant a name and makes the schema easier to audit.

CREATE DOMAIN money_amount AS numeric(12,2)
    CHECK (VALUE >= 0 AND VALUE <= 9999999999.99);

CREATE TABLE invoices_example_two (
    invoice_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    subtotal money_amount NOT NULL,
    tax money_amount NOT NULL DEFAULT 0,
    total money_amount GENERATED ALWAYS AS (subtotal + tax) STORED
);

INSERT INTO invoices_example_two (subtotal, tax)
VALUES (125.00, 10.00)
RETURNING subtotal, tax, total;

The deterministic output is a row with subtotal 125.00, tax 10.00, and total 135.00. The generated column prevents the stored total from disagreeing with its inputs. If an application tries to insert -1.00 into subtotal, PostgreSQL rejects the assignment before the row exists.

Domains are excellent for scalar rules that mean the same thing everywhere. They are weaker for rules that depend on other columns or other rows. For example, a domain can say an amount is nonnegative, but it cannot say an invoice total must equal the sum of its line items. That cross-row rule needs table constraints, triggers, transaction design, or a derived query rather than a scalar domain.

Example 3: Modeling Time Intervals With Range Types

Many schemas store starts_at and ends_at as separate timestamp columns. That is workable, but it hides the fact that the domain concept is an interval. PostgreSQL range types model intervals directly and provide operators such as overlap, containment, and adjacency.

CREATE TABLE room_bookings_example_three (
    booking_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL,
    booked_during tstzrange NOT NULL CHECK (NOT isempty(booked_during)),
    EXCLUDE USING gist (room_id WITH =, booked_during WITH &&)
);

INSERT INTO room_bookings_example_three (room_id, booked_during)
VALUES (101, tstzrange('2026-09-06 10:00+00', '2026-09-06 11:00+00', '[)'));

INSERT INTO room_bookings_example_three (room_id, booked_during)
VALUES (101, tstzrange('2026-09-06 11:00+00', '2026-09-06 12:00+00', '[)'));

The first two inserts succeed because the intervals are adjacent, not overlapping. The [) bound means the lower bound is included and the upper bound is excluded, so a booking ending at 11:00 does not collide with one starting at 11:00. An insert for room 101 from 10:30 to 10:45 fails because the GiST-backed exclusion constraint finds an overlap. A booking for a different room at the same time succeeds because the exclusion constraint compares both room_id and the range.

This example shows why type choice can remove application race conditions. Without the exclusion constraint, two concurrent transactions could both check for no overlap and then insert conflicting rows. With the constraint in the database, PostgreSQL arbitrates the conflict at write time.

JSONB And Semi-Structured Boundaries

jsonb is useful when part of a model is genuinely variable: integration payloads, user preferences, event metadata, or attributes whose keys change frequently. It is not a substitute for relational modeling. If a field is required for joins, filtering, authorization, uniqueness, or reporting, promote it to a typed column. Keep jsonb for the part whose shape is intentionally flexible.

A balanced design often combines both. Store stable identity and lifecycle columns relationally, then store optional metadata in jsonb with a check that ensures the JSON value has the expected top-level shape. Add expression indexes only for keys that are actually queried. Indexing every possible JSON path wastes write performance and storage.

Design Choices And Trade-Offs

Prefer the narrowest type that truthfully represents the domain. Use date for calendar dates without time-of-day, timestamptz for instants, numeric for exact decimal arithmetic, integer types for counts and identifiers, uuid for externally generated opaque identifiers, and range types for intervals. Avoid storing structured facts in delimited strings.

There are trade-offs. Narrow types reject bad data early, but they require migrations when the domain changes. Domains reduce repetition, but changing a domain affects every dependent column. Enums make invalid states impossible, but a lookup table is more flexible when business users add, retire, or describe values. jsonb absorbs change, but it gives up some static guarantees and can make reporting harder. The right design is the one that preserves important invariants while leaving genuinely volatile parts adaptable.

Failure Modes And Troubleshooting

Symptom: inserts fail with a check-constraint or domain violation after a migration. Cause: existing application values do not match the stricter model, or the application casts values into the domain too late. Diagnose: reproduce the failing insert, inspect pg_constraint for the exact rule, and run a query that finds rows or input examples outside the new range. Correct: backfill or normalize data first, deploy application validation, then add the constraint as a separate migration.

Symptom: time-based reports are off by a day for some users. Cause: the schema confused calendar dates with instants, or stored local times without time zone context. Diagnose: compare column types with the business question: birthday, billing date, and due date often use date; event occurrence usually uses timestamptz. Correct: migrate to the appropriate type and make display-time conversion an application concern.

Symptom: queries against JSONB metadata become slow as data grows. Cause: frequently filtered attributes stayed inside JSONB without expression indexes or typed columns. Diagnose: run EXPLAIN, identify sequential scans and repeated casts from JSON text. Correct: promote stable keys to columns, or create focused expression indexes for the exact predicates used.

Symptom: overlapping reservations appear during busy periods. Cause: overlap checks were implemented as read-before-write application logic rather than a database constraint. Diagnose: inspect whether the table has an exclusion constraint or only an application query. Correct: model the interval with a range type and enforce non-overlap with an exclusion constraint inside PostgreSQL.

Security, Performance, And Reliability Implications

Types reduce the amount of malformed data that reaches sensitive logic. A typed identifier prevents accidental joins against display names. A domain can stop obviously invalid emails or money values before downstream jobs process them. Constraints also help incident recovery because corrupt states are less likely to exist in the first place.

Performance depends on matching types to operators and indexes. Numeric comparisons on numeric columns can use numeric operator classes. Range overlap can use GiST indexes. JSONB containment can use GIN indexes. Repeated casts in predicates, such as casting a text column to a timestamp in every query, often block efficient index use and hide data quality problems.

Reliability improves when invariants live near the data. Multiple services, scripts, migrations, and manual maintenance sessions all pass through the same database rules. Application validation is still valuable for user experience, but database validation is the final authority for persisted state.

Hands-On Lab

Prerequisites: a PostgreSQL database where you can create tables, domains, and extensions. Use a scratch database or schema because the lab creates and drops objects.

  1. Create a scratch schema with CREATE SCHEMA lesson_types; and set it with SET search_path TO lesson_types;.
  2. Create the email_address and money_amount domains from the earlier examples.
  3. Create customers, orders_example_one, and invoices_example_two.
  4. Insert one valid customer, one valid order, and one valid invoice. Verify returned values with SELECT.
  5. Attempt one invalid email, one negative amount, and one invalid status. Confirm PostgreSQL rejects each write.
  6. If your environment supports GiST exclusion constraints for equality on bigint, create the booking table and test adjacent and overlapping ranges. If needed, install the required extension for equality support in a scratch environment.
  7. Run EXPLAIN on a lookup by status and on a range-overlap query after adding representative rows. Record whether the plan matches your indexing expectations.

Verification: valid rows are stored with typed values, generated totals match their inputs, invalid writes fail, and overlapping bookings for the same room are rejected. Cleanup: run DROP SCHEMA lesson_types CASCADE; to remove all lab objects.

Assessment Exercises

  1. A product catalog stores price, SKU, publication state, and optional integration metadata. Which fields should be typed columns, which constraints would you add, and what, if anything, belongs in jsonb?
  2. Choose between an enum, a check constraint, and a lookup table for subscription status. Explain how your answer changes if non-engineers can add statuses.
  3. Design a PostgreSQL model for employee vacation dates. Decide whether to use two dates, a range type, or both, and explain how you would prevent overlapping approved vacations for the same employee.
  4. An application stores timestamps as text because several import feeds disagree. Describe a migration path that improves the type model without breaking ingestion.
  5. Find one repeated scalar rule in an existing schema and decide whether it should become a domain. What dependent tables would be affected by changing it later?

Summary

Domain modeling in PostgreSQL starts with choosing types that match the real business concepts. Built-in types give PostgreSQL correct operators, casts, storage behavior, and index options. Domains name reusable scalar rules. Constraints, foreign keys, generated columns, range types, and exclusion constraints enforce rules that application code alone can miss under concurrency. Use flexible types such as jsonb deliberately, not as a default escape hatch. A strong schema does not remove the need for application validation; it makes the database the durable authority for the facts it stores.