PostgreSQL: Using ‘CREATE DOMAIN’ to create custom data types

Introduction

In PostgreSQL, the CREATE DOMAIN command is a powerful feature that enables you to define custom data types with constraints to maintain data integrity. This tutorial delves into using domains, enhancing database design with clear examples.

Understanding CREATE DOMAIN

Before diving into code, let’s clarify what a domain is in the context of PostgreSQL. A domain is essentially a data type with optional constraints that define valid values that columns of this type can contain. Think of it as creating a custom rulebook for a data type.

Here’s a simple example that creates a domain for a positive integer:

CREATE DOMAIN positive_integer AS INT
CHECK (VALUE > 0);

This domain ensures that any column defined with the type positive_integer cannot contain a negative number or zero.

Basic Usage of Domains

Let’s start by creating a basic domain that restricts a text field to a specific format:

CREATE DOMAIN email_type AS TEXT
CHECK (VALUE ~* '^[^@]+@[^@]+\.[^@]{2,}

Here, email_type is now a domain that ensures any value assigned to it resembles an email address.

Once you have defined a domain, you can use it just like any other data type when creating a table:

CREATE TABLE users (
    name TEXT,
    email email_type
);

The email column is constrained by the email_type domain, enforcing valid email format. Attempting to insert an invalid email will result in an error.

Advanced Domain Usage

Moving onto more complex scenarios, PostgreSQL allows domains to be based on other domains, adding layers of constraints when necessary. For example:

CREATE DOMAIN business_email AS email_type
CHECK (VALUE LIKE '%@mybusiness.com');

This business_email domain builds upon the previously defined email_type to specifically target email addresses belonging to ‘mybusiness.com’.

You can also alter existing domains to tighten or loosen constraints using the ALTER DOMAIN command:

ALTER DOMAIN email_type
ADD CONSTRAINT email_must_have_dot
CHECK (VALUE LIKE '%.%');

This adds an additional check to ensure that email addresses contain at least one dot.

Domain Constraints

Domains can have several constraints such as NOT NULL, CHECK, and others. There’s also the possibility to name constraints for easier identification:

CREATE DOMAIN age_category AS INTEGER
CONSTRAINT must_be_valid_age
CHECK (VALUE > 0 AND VALUE < 130);

This domain will not accept an age less than 1 or greater than 129. Naming the constraint allows you to quickly reference and manage it in the future.

Using Domains with Functions

Domains aren’t just for table columns. They can also be used with function arguments and return types, fostering consistency throughout the database. Here’s a function example that accepts a business_email domain:

CREATE FUNCTION add_business_contact(email business_email, contact_name TEXT)
RETURNS VOID AS $
BEGIN
    -- Function body to add a business contact
END;
$ LANGUAGE plpgsql;

This ensures that the email parameter passed to the function adheres to the business_email constraints.

Managing Domain Dependencies

A significant aspect of domains is handling dependencies. Removing or altering a domain in use can be tricky. PostgreSQL, however, provides tools to navigate this. For instance, to drop a domain without affecting dependent objects, you can use the CASCADE option:

DROP DOMAIN business_email CASCADE;

This command will remove the domain and any constraints associated with it from any tables or functions where it’s in use.

Conclusion

Domains are a robust feature in PostgreSQL that can streamline data validation and consistency across your database. With careful planning and understanding of their constraints, they can become a cornerstone of a reliable and maintainable database schema. Always test and document any changes to domain definitions to avoid potential disruptions in your application.