What is Domain in PostgreSQL: Definition and Usage

In PostgreSQL, a domain is a user-defined data type based on an existing type with optional constraints. It acts like a custom type that enforces rules on the data stored, helping keep data consistent and valid.

How It Works

Think of a domain in PostgreSQL as a mold or template for data. Instead of just using a basic data type like integer or text, you create a domain that adds rules on top of that type. For example, you can say the number must be positive or the text must match a pattern.

This is similar to setting up a custom label for a type of data you use often, so you don't have to repeat the same rules everywhere. When you use a domain in a table, PostgreSQL automatically checks the data against the rules you defined, preventing invalid data from being saved.

Example

This example creates a domain for a positive integer and uses it in a table to ensure only positive ages are stored.

sql

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

CREATE TABLE people (
  id serial PRIMARY KEY,
  name text NOT NULL,
  age positive_int
);

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

Output

INSERT 0 1
ERROR:  value for domain positive_int violates check constraint "positive_int_check"

When to Use

Use domains when you want to enforce consistent rules on a type of data across many tables. For example, if you have a phone number format, email format, or age limit that applies in multiple places, a domain helps you define it once and reuse it.

This reduces errors and makes your database easier to maintain because the rules are centralized. It is especially useful in large projects or when multiple developers work on the same database.

Key Points

• A domain is a custom data type based on an existing type with added constraints.
• It helps enforce data validity and consistency across tables.
• Domains simplify maintenance by centralizing rules.
• They can include checks like ranges, patterns, or non-null requirements.

Key Takeaways

A domain in PostgreSQL is a user-defined type with constraints based on an existing type.

Domains enforce data rules automatically wherever they are used.

They help keep data consistent and reduce repeated code.

Use domains to centralize validation logic for common data types.

Domains improve database maintainability and data integrity.