PostgreSQL Domain Types

Introduction

When designing a database, ensuring data integrity is crucial. PostgreSQL offers a powerful feature called domain types that allows you to create custom data types with constraints. These domain types act as wrappers around base types, adding validation rules that ensure your data meets specific requirements.

Think of domain types as creating your own specialized data types that enforce business rules directly at the database level. This helps maintain data consistency and can make your database schema more self-documenting and maintainable.

What Are Domain Types?

A domain in PostgreSQL is a user-defined data type with optional constraints. Once created, a domain can be used anywhere a built-in data type can be used. Domains are particularly useful for:

1. Enforcing consistent constraints across multiple tables
2. Making database schemas more readable and self-documenting
3. Centralizing business rules in your data model
4. Simplifying schema changes when business rules evolve

Creating Domain Types

The basic syntax for creating a domain is:

sql

CREATE DOMAIN domain_name AS base_type
  [DEFAULT default_expression]
  [CONSTRAINT constraint_name constraint_definition ...];

Let's break down the components:

• domain_name: The name you give to your custom domain
• base_type: Any valid PostgreSQL data type
• DEFAULT: Optional default value for columns using this domain
• CONSTRAINT: Optional constraints applied to the domain

Simple Domain Examples

Example 1: Creating a Positive Integer Domain

sql

CREATE DOMAIN positive_int AS integer
  CONSTRAINT positive_check CHECK (VALUE > 0);

This domain ensures that any value assigned to it must be a positive integer.

Example 2: Creating an Email Address Domain

sql

CREATE DOMAIN email_address AS text
  CONSTRAINT valid_email CHECK (
    VALUE ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
  );

This domain uses a regular expression to validate that values conform to a basic email format.

Using Domain Types in Tables

Once you've created a domain, you can use it just like any built-in data type:

sql

CREATE TABLE employees (
  id serial PRIMARY KEY,
  name text NOT NULL,
  email email_address NOT NULL,
  age positive_int,
  salary positive_int
);

Now, let's attempt to insert data into this table:

sql

-- This will succeed
INSERT INTO employees (name, email, age, salary)
VALUES ('John Doe', '[email protected]', 30, 50000);

-- This will fail (negative salary)
INSERT INTO employees (name, email, age, salary)
VALUES ('Jane Smith', '[email protected]', 25, -1000);

-- This will fail (invalid email format)
INSERT INTO employees (name, email, age, salary)
VALUES ('Bob Johnson', 'not-an-email', 40, 60000);

Practical Applications

Example: Domain Types for a Product Inventory System

Let's design a product inventory system with domain types to enforce business rules:

sql

-- Create domains for our inventory system
CREATE DOMAIN product_code AS varchar(10)
  CONSTRAINT valid_product_code CHECK (
    VALUE ~ '^[A-Z]{2}\d{8}$'
  );

CREATE DOMAIN inventory_quantity AS integer
  CONSTRAINT positive_quantity CHECK (VALUE >= 0);

CREATE DOMAIN price_usd AS decimal(10,2)
  CONSTRAINT positive_price CHECK (VALUE >= 0);

CREATE DOMAIN product_status AS text
  CONSTRAINT valid_status CHECK (
    VALUE IN ('available', 'discontinued', 'out_of_stock')
  );

-- Create the products table using these domains
CREATE TABLE products (
  id serial PRIMARY KEY,
  code product_code UNIQUE NOT NULL,
  name text NOT NULL,
  quantity inventory_quantity NOT NULL DEFAULT 0,
  price price_usd NOT NULL,
  status product_status NOT NULL DEFAULT 'available'
);

With this design:

• Product codes must follow a specific format (2 uppercase letters followed by 8 digits)
• Inventory quantities cannot be negative
• Prices must be positive
• Product status must be one of the predefined values

Example: Domain Types for Personal Information

sql

CREATE DOMAIN us_zipcode AS varchar(5)
  CONSTRAINT valid_zipcode CHECK (
    VALUE ~ '^\d{5}$'
  );

CREATE DOMAIN us_phone AS varchar(12)
  CONSTRAINT valid_phone CHECK (
    VALUE ~ '^\d{3}-\d{3}-\d{4}$'
  );

CREATE DOMAIN us_ssn AS varchar(11)
  CONSTRAINT valid_ssn CHECK (
    VALUE ~ '^\d{3}-\d{2}-\d{4}$'
  );

CREATE TABLE customers (
  id serial PRIMARY KEY,
  name text NOT NULL,
  phone us_phone NOT NULL,
  ssn us_ssn UNIQUE NOT NULL,
  zipcode us_zipcode NOT NULL
);

This ensures consistent formatting for personal information across your database.

Advanced Domain Features

Adding NOT NULL Constraints

You can enforce that a domain never accepts NULL values:

sql

CREATE DOMAIN required_text AS text NOT NULL;

Using Domains with Complex Constraints

Domains can use complex CHECK constraints:

sql

CREATE DOMAIN age_range AS integer
  CONSTRAINT valid_age CHECK (
    VALUE >= 18 AND VALUE <= 100
  );

Domains with Default Values

You can set default values for domains:

sql

CREATE DOMAIN status_flag AS text
  DEFAULT 'active'
  CONSTRAINT valid_status CHECK (
    VALUE IN ('active', 'inactive', 'pending')
  );

Modifying Domain Constraints

You can alter existing domains:

sql

-- Add a new constraint
ALTER DOMAIN positive_int ADD CONSTRAINT max_value CHECK (VALUE <= 1000);

-- Remove a constraint
ALTER DOMAIN positive_int DROP CONSTRAINT max_value;

-- Rename a domain
ALTER DOMAIN positive_int RENAME TO positive_integer;

Domain Information Schema

PostgreSQL provides views to query information about domains:

sql

-- List all domains in the current database
SELECT domain_name, data_type, domain_default
FROM information_schema.domains
WHERE domain_schema = 'public';

-- View constraints for a specific domain
SELECT constraint_name, check_clause 
FROM information_schema.domain_constraints
JOIN information_schema.check_constraints USING (constraint_name)
WHERE domain_name = 'positive_int';

Domain Types vs. Custom Types

PostgreSQL offers both domain types and custom types (created with CREATE TYPE). Here's how they differ:

Feature	Domain Types	Custom Types
Base	Built on existing types	Can create entirely new types
Constraints	Can add constraints	No built-in constraints
Usage	Simple data validation	Complex data structures
Implementation	Simple to create	More complex to implement

Domains are perfect for simple validation rules, while custom types are better for complex data structures.

Tips for Using Domain Types Effectively

1. Use meaningful names: Choose descriptive names that reflect the domain's purpose
2. Keep constraints focused: Each domain should have a clear, specific purpose
3. Document domains: Comment your domains to explain their business purpose
4. Consider performance: Complex CHECK constraints can impact performance
5. Domain reuse: Design domains to be reusable across multiple tables

Common Pitfalls

1. Too many domains: Creating domains for every column can lead to maintenance overhead
2. Overly complex validation: Regular expressions can become difficult to maintain
3. Forgetting inheritance: Domains inherit all constraints from their base type
4. Not considering NULL values: Be explicit about NULL handling in constraints

Summary

PostgreSQL domain types provide a powerful way to enforce data integrity across your database. By creating custom domains, you can:

• Centralize business rules in your data model
• Improve schema readability and self-documentation
• Ensure consistent data validation
• Simplify schema maintenance

Domain types are particularly valuable for beginner database designers as they help establish good data modeling practices from the start.

Exercises

1. Create a domain for validating URLs with a CHECK constraint using a regular expression
2. Design domains for a library database that includes book ISBNs, library card numbers, and checkout limits
3. Create a domain for storing RGB color codes (format: #RRGGBB)
4. Modify an existing domain to add additional constraints without breaking existing data
5. Write queries to find all columns in your database that use a specific domain type

Additional Resources

• PostgreSQL Official Documentation on Domains
• PostgreSQL Data Types
• CHECK Constraints