Domain types for validation in PostgreSQL

Introduction

Domain types help you make sure data follows rules before it goes into your database. This keeps your data clean and correct.

When you want to check that a phone number always has 10 digits.

When you want to ensure an email address has a proper format.

When you want to limit a column to only positive numbers.

When you want to reuse the same validation rules in many tables.

When you want to avoid mistakes by catching bad data early.

Syntax

PostgreSQL

CREATE DOMAIN domain_name AS base_data_type
  [ DEFAULT default_expression ]
  [ CONSTRAINT constraint_name CHECK (expression) ];

A domain is like a custom data type with rules.

You use CREATE DOMAIN once, then use the domain as a column type.

Examples

This domain ensures the phone number is exactly 10 digits and only numbers.

PostgreSQL

CREATE DOMAIN phone_number AS TEXT
  CHECK (LENGTH(VALUE) = 10 AND VALUE ~ '^[0-9]+$');

This domain allows only positive integers.

PostgreSQL

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

This domain checks if the text looks like an email address (simple pattern).

PostgreSQL

CREATE DOMAIN email_address AS TEXT
  CHECK (VALUE ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$');

Sample Program

This example creates a domain for age that must be between 0 and 120. Then it creates a table using that domain. The first insert works, the second fails because age is negative.

PostgreSQL

CREATE DOMAIN age_domain AS INTEGER
  CHECK (VALUE >= 0 AND VALUE <= 120);

CREATE TABLE people (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  age age_domain
);

INSERT INTO people (name, age) VALUES ('Alice', 30);
INSERT INTO people (name, age) VALUES ('Bob', -5);

Important Notes

Use the keyword VALUE inside CHECK to refer to the data being checked.

Domains help keep your data rules in one place, making your database easier to manage.

When inserting or updating, if data breaks domain rules, PostgreSQL will stop the action with an error.

Summary

Domains are custom data types with built-in validation rules.

They help keep data clean by enforcing rules automatically.

You create a domain once and use it like any other data type in tables.