Domain types for validation in PostgreSQL - Deep Dive

Overview - Domain types for validation

What is it?

Domain types in PostgreSQL are custom data types that let you add rules to existing data types. They help ensure that data stored in a database follows specific conditions, like a number being positive or a string matching a pattern. Domains act like templates with built-in checks, so you don't have to repeat validation rules everywhere. This makes your data more reliable and your database easier to manage.

Why it matters

Without domain types, you would have to write the same validation rules repeatedly for each table or column, increasing the chance of mistakes and inconsistent data. Domains centralize validation, so errors are caught early and data stays clean. This saves time, reduces bugs, and makes your database trustworthy for real-world applications like banking or inventory management.

Where it fits

Before learning domain types, you should understand basic data types and constraints in SQL. After mastering domains, you can explore advanced topics like user-defined types, triggers, and stored procedures to build even more powerful data validation and logic inside the database.

Mental Model

Core Idea

A domain type is a named data type with built-in rules that automatically check data validity whenever you use it.

Think of it like...

Think of a domain type like a cookie cutter with a shape and size. Every cookie (data) made with that cutter fits the shape perfectly, so you don't have to check each cookie manually.

┌───────────────┐
│ Base Data Type│
│  (e.g. INT)   │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ Domain Type (e.g. PositiveInt)│
│  - Base type: INT           │
│  - Constraint: VALUE > 0    │
└─────────────┬───────────────┘
              │
              ▼
┌─────────────────────────────┐
│ Column using Domain Type     │
│  - Only positive integers    │
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding basic data types

Concept: Learn what data types are and how they define the kind of data a column can hold.

In PostgreSQL, data types like INTEGER, TEXT, and DATE tell the database what kind of data to expect. For example, INTEGER stores whole numbers, TEXT stores letters and symbols, and DATE stores calendar dates. These types help the database organize and validate data at a basic level.

Result

You know how to choose a data type for a column to store the right kind of data.

Understanding data types is essential because domain types build on them by adding extra rules.

2

Foundation

Using constraints for data rules

Concept: Learn how constraints add rules to columns to restrict what data is allowed.

Constraints like CHECK, NOT NULL, UNIQUE, and FOREIGN KEY limit the data you can put in a column. For example, a CHECK constraint can require a number to be positive: CHECK (VALUE > 0). These rules help keep data accurate and meaningful.

Result

You can enforce simple rules on columns to prevent invalid data.

Constraints are the building blocks for domain types, which package these rules for reuse.

3

Intermediate

Creating a domain type with constraints

🤔Before reading on: do you think a domain can have multiple constraints or just one? Commit to your answer.

Concept: Learn how to define a domain type that wraps a base type and adds one or more constraints.

You create a domain using CREATE DOMAIN name AS base_type WITH CONSTRAINTS. For example: CREATE DOMAIN positive_int AS INTEGER CHECK (VALUE > 0); This domain ensures any column using it only accepts positive integers.

Result

A reusable domain type that enforces validation automatically.

Knowing that domains can bundle multiple constraints helps you create powerful, reusable data rules.

4

Intermediate

Applying domain types to table columns

🤔Before reading on: do you think you can mix domain types with regular types in the same table? Commit to your answer.

Concept: Learn how to use domain types as column data types in tables to enforce validation.

When creating or altering a table, you can specify a column's type as a domain. For example: CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, quantity positive_int NOT NULL ); The quantity column now only accepts positive integers, enforced by the domain.

Result

Table columns automatically validate data using the domain's rules.

Using domains in tables centralizes validation and reduces repeated code.

5

Intermediate

Modifying and dropping domain types

🤔Before reading on: do you think you can change a domain's constraints after creation? Commit to your answer.

Concept: Learn how to update or remove domain types and understand the impact on dependent tables.

You can drop a domain with DROP DOMAIN name; but only if no table uses it. To change constraints, you must drop and recreate the domain or use ALTER DOMAIN to add/drop constraints. For example: ALTER DOMAIN positive_int ADD CONSTRAINT max_limit CHECK (VALUE < 1000); This adds a max value rule.

Result

You can evolve domain rules but must manage dependencies carefully.

Understanding domain lifecycle helps maintain data integrity as requirements change.

6

Advanced

Combining domains with other constraints

🤔Before reading on: do you think table-level constraints override domain constraints? Commit to your answer.

Concept: Learn how domain constraints interact with table-level constraints and indexes.

Domain constraints always apply first when inserting or updating data. Table-level constraints add extra rules on top. For example, a domain may require positive numbers, and a table-level UNIQUE constraint ensures no duplicates. Both work together to keep data valid and unique.

Result

Data is validated by multiple layers of rules for stronger guarantees.

Knowing how constraints stack prevents conflicts and ensures comprehensive validation.

7

Expert

Performance and internal behavior of domains

🤔Before reading on: do you think domains add runtime overhead to queries? Commit to your answer.

Concept: Understand how PostgreSQL processes domain types internally and their effect on performance.

Domains are essentially wrappers around base types with attached constraints. At runtime, PostgreSQL treats domain columns like their base type but runs constraint checks on data modification. This adds minimal overhead because checks happen only on insert/update, not on selects. Domains do not affect storage size or indexing.

Result

Domains provide validation with almost no runtime cost during data retrieval.

Understanding internal mechanics helps you confidently use domains without fearing performance loss.

Under the Hood

PostgreSQL stores domain types as their base data type internally. When data is inserted or updated in a domain column, the database automatically runs the domain's CHECK constraints to validate the data. If the data fails any constraint, the operation is rejected. During queries, the domain behaves like the base type, so there is no extra processing cost. Domains also support inheritance of constraints and can be nested.

Why designed this way?

Domains were designed to promote code reuse and consistency in data validation. Before domains, developers had to repeat constraints on every table column, leading to errors and maintenance headaches. By wrapping base types with constraints, domains centralize validation logic. This design balances flexibility, performance, and ease of use without changing the core data storage or query engine.

┌───────────────┐
│ User inserts  │
│ data into     │
│ domain column │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Domain CHECK  │
│ constraints   │
│ validate data │
└──────┬────────┘
       │ pass
       ▼
┌───────────────┐
│ Store as base │
│ data type     │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do domain types store data differently than their base types? Commit to yes or no.

Common Belief: Domain types store data in a special format different from base types.

Reality: Domain types store data exactly like their base types; they only add validation rules on top.

Why it matters: Believing domains change storage can lead to unnecessary worries about performance or compatibility.

Quick: Can you skip domain constraints by inserting data directly into the base type column? Commit to yes or no.

Common Belief: You can bypass domain constraints by inserting data directly into the base type column.

Reality: Domain constraints always apply when using the domain type; you cannot bypass them unless you avoid the domain type entirely.

Why it matters: Thinking constraints can be bypassed weakens trust in data integrity and leads to inconsistent data.

Quick: Do domain constraints override table-level constraints if they conflict? Commit to yes or no.

Common Belief: Domain constraints override any conflicting table-level constraints.

Reality: Both domain and table-level constraints apply independently; conflicts must be resolved by the developer.

Why it matters: Misunderstanding this can cause unexpected validation failures or data acceptance.

Quick: Are domain types a replacement for all validation logic in applications? Commit to yes or no.

Common Belief: Domain types replace the need for any validation in application code.

Reality: Domains help enforce data rules at the database level but do not replace all application-level validation, especially for complex logic.

Why it matters: Overreliance on domains alone can lead to missing important checks and poor user experience.

Expert Zone

1

Domains can be nested by creating a domain over another domain, stacking constraints elegantly.

2

Constraints in domains are always checked before table-level constraints, which can affect error messages and debugging.

3

Domains do not support default values directly; defaults must be set at the table column level.

When NOT to use

Avoid domains when validation rules depend on multiple columns or complex logic that requires triggers or stored procedures. Also, if you need default values or mutable constraints per table, domains may be too rigid. In such cases, use CHECK constraints directly on tables or application-level validation.

Production Patterns

In production, domains are used to enforce consistent formats like email addresses, phone numbers, or positive quantities across many tables. They reduce duplication and bugs in large schemas. Teams often combine domains with schema migrations to evolve validation rules safely over time.

Connections

Object-oriented programming (OOP) classes

Domains are like classes that wrap basic types with added rules and behaviors.

Understanding domains as data types with encapsulated validation is similar to how classes encapsulate data and methods, helping bridge database and programming concepts.

Data validation in web forms

Both domains and web form validation enforce rules to keep data clean, but domains do it at the database level.

Knowing that domains provide a last line of defense after user input validation helps design robust systems.

Type systems in programming languages

Domains extend base types with constraints, similar to how type systems add refinement types or subtypes.

Recognizing domains as a form of refined types deepens understanding of type safety and correctness across software layers.

Common Pitfalls

#1 Trying to create a domain with a constraint referencing a column name.

Wrong approach:

CREATE DOMAIN positive_age AS INTEGER CHECK (age > 0);

Correct approach:

CREATE DOMAIN positive_age AS INTEGER CHECK (VALUE > 0);

Root cause:

Misunderstanding that domain constraints use VALUE as a placeholder for the input, not column names.

#2 Dropping a domain without removing dependent columns first.

Wrong approach:

DROP DOMAIN positive_int;

Correct approach:

ALTER TABLE orders ALTER COLUMN quantity TYPE INTEGER;
DROP DOMAIN positive_int;

Root cause:

Not realizing that domains cannot be dropped while in use by table columns.

#3 Assuming domain constraints apply during SELECT queries.

Wrong approach:

Expecting SELECT * FROM orders WHERE quantity = -5; to fail due to domain constraints.

Correct approach:

Domain constraints only apply on INSERT or UPDATE, so SELECT queries do not trigger validation.

Root cause:

Confusing data validation timing with query filtering.

Key Takeaways

Domain types in PostgreSQL let you create reusable data types with built-in validation rules.

They help keep data clean by enforcing constraints automatically whenever data is inserted or updated.

Domains build on base data types and constraints but do not change how data is stored or queried.

Using domains reduces repeated code and mistakes, making database schemas easier to maintain.

Understanding domain lifecycle and interaction with other constraints is key to using them effectively in production.