User-Defined Data Types in PostgreSQL

Beyond the built-in types, PostgreSQL lets you create custom data types with two statements: CREATE DOMAIN and CREATE TYPE. Domains centralize column-level validation; composite types create structured records for functions and table columns.

CREATE DOMAIN: Reusable Column Constraints

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

-- Constraint duplicated on three columns
CREATE TABLE orders (
  id           SERIAL PRIMARY KEY,
  buyer_email  VARCHAR NOT NULL CHECK (buyer_email ~* '^[^@]+@[^@]+\.[^@]+$'),
  seller_email VARCHAR NOT NULL CHECK (seller_email ~* '^[^@]+@[^@]+\.[^@]+$'),
  cc_email     VARCHAR CHECK (cc_email ~* '^[^@]+@[^@]+\.[^@]+$')
);

Define the constraint once with a domain and reuse it:

-- Create the domain once
CREATE DOMAIN email_address AS VARCHAR
  CHECK (VALUE ~* '^[^@]+@[^@]+\.[^@]+$');

-- Use it wherever an email column is needed
CREATE TABLE orders (
  id           SERIAL PRIMARY KEY,
  buyer_email  email_address NOT NULL,
  seller_email email_address NOT NULL,
  cc_email     email_address
);

CREATE TABLE shipments (
  id           SERIAL PRIMARY KEY,
  notify_email email_address NOT NULL,
  order_id     INTEGER       NOT NULL REFERENCES orders(id)
);

Inserting a value that violates the domain raises an error:

INSERT INTO orders (buyer_email, seller_email)
VALUES ('not-an-email', 'vendor@example');
-- ERROR: value for domain email_address violates check constraint "email_address_check"

INSERT INTO orders (buyer_email, seller_email)
VALUES ('buyer@example', 'vendor@example');
-- OK

Listing and Managing Domains

-- List all domains in psql
\dD

-- Or query the catalog
SELECT typname AS domain_name, pg_catalog.format_type(typbasetype, typtypmod) AS base_type
FROM pg_catalog.pg_type
JOIN pg_catalog.pg_namespace ON pg_namespace.oid = pg_type.typnamespace
WHERE typtype = 'd' AND nspname = 'public';

-- Add a new constraint to an existing domain
ALTER DOMAIN email_address
  ADD CONSTRAINT no_whitespace CHECK (VALUE !~ '\s');

-- Drop the domain (fails if columns use it; add CASCADE to force)
DROP DOMAIN email_address CASCADE;

CREATE TYPE: Composite Types for Functions

Composite types group multiple fields into a single named structure, most commonly used as function return types:

-- Define a composite return type for order summary data
CREATE TYPE order_summary AS (
  order_id     INTEGER,
  product_sku  VARCHAR(50),
  total_amount NUMERIC(10,2),
  placed_at    TIMESTAMPTZ
);

-- Use the composite type as a function return type
CREATE OR REPLACE FUNCTION get_order_summary(p_order_id INTEGER)
RETURNS order_summary AS $$
  SELECT id, product_sku, quantity * unit_price, placed_at
  FROM orders
  WHERE id = p_order_id;
$$ LANGUAGE SQL;

-- Call the function
SELECT * FROM get_order_summary(42);

Managing Composite Types

-- Alter a composite type
ALTER TYPE order_summary ADD ATTRIBUTE status VARCHAR(20);
ALTER TYPE order_summary DROP ATTRIBUTE status;

-- List all user-defined types in psql
\dT

-- With details
\dT+

-- Drop a composite type
DROP TYPE IF EXISTS order_summary;

Testing with Vela

Domain and composite type changes affect every table and function that uses them. Before adding a new constraint to a domain that is shared across 10 tables, create a database branch with production data, run ALTER DOMAIN ... ADD CONSTRAINT NOT VALID, then VALIDATE CONSTRAINT — checking which rows (if any) violate the new constraint — before applying to the live database.

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 — adding a new constraint does not retroactively validate existing rows unless you run VALIDATE 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.
• Add NOT VALID when adding new domain constraints in production to avoid long table locks, then validate separately.