User-defined Data Types | PostgreSQL Tutorial

PostgreSQL user-defined data types

Beyond the built-in types, PostgreSQL lets you create custom data types with two statements:

CREATE DOMAIN — wraps an existing type with constraints (NOT NULL, CHECK). Useful for centralizing column-level validation.
CREATE TYPE — defines a composite type (a record with named fields). Used as function return types or structured data containers.

CREATE DOMAIN: reusable column constraints

Without a domain, you repeat the same constraint on every column that must satisfy it:

CREATE TABLE mailing_list (
  id         SERIAL PRIMARY KEY,
  first_name VARCHAR NOT NULL,
  last_name  VARCHAR NOT NULL,
  email      VARCHAR NOT NULL,
  CHECK (first_name !~ 's' AND last_name !~ 's')
);

With a domain, define the constraint once and reuse it:

-- Create a domain that rejects NULL and whitespace
CREATE DOMAIN contact_name AS VARCHAR
  NOT NULL
  CHECK (value !~ 's');

-- Use the domain as a column type
CREATE TABLE mailing_list (
  id         SERIAL PRIMARY KEY,
  first_name contact_name,
  last_name  contact_name,
  email      VARCHAR NOT NULL
);

Inserting a value that violates the domain constraint raises an error:

-- This fails because 'Jame V' contains a space
INSERT INTO mailing_list (first_name, last_name, email)
VALUES ('Jame V', 'Doe', '[email protected]');
-- ERROR: value for domain contact_name violates check constraint "contact_name_check"

-- This succeeds
INSERT INTO mailing_list (first_name, last_name, email)
VALUES ('Jane', 'Doe', '[email protected]');

Listing domains

-- In psql
dD

-- In SQL
SELECT typname
FROM pg_catalog.pg_type
JOIN pg_catalog.pg_namespace
  ON pg_namespace.oid = pg_type.typnamespace
WHERE typtype = 'd'
  AND nspname = 'public';

CREATE TYPE: composite types for functions

Composite types let a function return multiple columns as a structured record:

-- Define the composite return type
CREATE TYPE film_summary AS (
  film_id      INT,
  title        VARCHAR,
  release_year SMALLINT
);

-- Use it as a function return type
CREATE OR REPLACE FUNCTION get_film_summary(f_id INT)
RETURNS film_summary AS $$
  SELECT film_id, title, release_year
  FROM film
  WHERE film_id = f_id;
$$ LANGUAGE SQL;

-- Call the function
SELECT * FROM get_film_summary(40);

Managing user-defined types

-- Alter a domain: add a new constraint
ALTER DOMAIN contact_name ADD CONSTRAINT no_digits CHECK (value !~ '[0-9]');

-- Drop a domain
DROP DOMAIN contact_name;

-- Drop a composite type
DROP TYPE film_summary;

-- List all user-defined types in psql
dT

-- Or with details
dT+

Production tips

Use domains to enforce consistent validation across multiple tables — change the constraint in one place rather than hunting down every column definition.
Domain constraints run at INSERT and UPDATE time — they do not retroactively validate existing rows when you add a new constraint.
Composite types are most useful as function return types. For structured column data, consider JSONB or a related table instead.
Document your custom types with COMMENT ON TYPE type_name IS 'description'; so the intent is visible in schema introspection tools.

Reference: PostgreSQL documentation — CREATE DOMAIN.

Frequently Asked Questions

What is a PostgreSQL domain?

A domain is a user-defined data type built on top of an existing base type, with optional constraints such as NOT NULL and CHECK. Domains centralize validation logic — instead of repeating the same CHECK constraint on every column, you define it once in the domain and reuse it. A domain has a unique name within its schema scope.

How do you create a domain in PostgreSQL?

Use CREATE DOMAIN: CREATE DOMAIN contact_name AS VARCHAR NOT NULL CHECK (value !~ '\s'); This creates a type that must not be null and must not contain whitespace. Then use it as a column type: first_name contact_name. The domain enforces the constraint wherever it is used.

What is a composite type in PostgreSQL?

A composite type groups multiple fields with their types into a single named type. It is most commonly used as the return type of a function that needs to return multiple values. Define it with: CREATE TYPE film_summary AS (film_id INT, title VARCHAR, release_year SMALLINT); Then use it as RETURNS film_summary in a function declaration.

How do you alter or drop a domain or type?

Use ALTER DOMAIN domain_name to add, drop, or modify constraints. Use DROP DOMAIN domain_name to remove it (add CASCADE to also drop dependent objects). For composite types, use ALTER TYPE type_name ADD ATTRIBUTE / DROP ATTRIBUTE, and DROP TYPE type_name to remove it.

How do you list all domains and user-defined types in PostgreSQL?

In psql, use \dD to list domains and \dT or \dT+ to list user-defined types. In SQL, query pg_catalog.pg_type joined with pg_catalog.pg_namespace: SELECT typname FROM pg_catalog.pg_type JOIN pg_catalog.pg_namespace ON pg_namespace.oid = pg_type.typnamespace WHERE typtype = 'd' AND nspname = 'public';