Custom Types

PostgreSQL is built around an extensible type system — the built-in types like integer, text, and timestamp are just the ones that ship in the box. You can define your own types with CREATE TYPE, giving names to structures that show up repeatedly in your schema. This page covers the two most useful forms: composite types, which bundle several fields into one reusable structure, and domains, which layer a named constraint on top of an existing base type.

Composite types

A composite type is a structured type made up of multiple named fields, similar to a row in a table, but usable as the type of a single column, a function parameter, or a function's return value. It is defined with CREATE TYPE ... AS, followed by a list of fields much like a table definition.

Defining a composite address type

SQL

CREATE TYPE address AS (
  street      TEXT,
  city        TEXT,
  postal_code TEXT,
  country     TEXT
);

Once defined, address can be used as the type of a column, exactly like any built-in type. This is convenient when several tables in a schema need the same shape of data — a customer's shipping address and a warehouse's location, for instance — without duplicating four separate columns in every table.

Using a composite type as a column

SQL

CREATE TABLE customers (
  customer_id  SERIAL PRIMARY KEY,
  name         TEXT NOT NULL,
  email        TEXT UNIQUE NOT NULL,
  home_address address
);

CREATE TABLE warehouses (
  warehouse_id SERIAL PRIMARY KEY,
  name         TEXT NOT NULL,
  location     address
);

Inserting a composite value uses row-constructor syntax, and reading its fields back out uses dot notation (often with parentheses around the column reference, to keep the parser happy).

Inserting and reading composite fields

SQL

INSERT INTO customers (name, email, home_address)
VALUES (
  'Jordan Blake',
  'jordan@example.com',
  ROW('221B Baker Street', 'London', 'NW1', 'UK')
);

SELECT name, (home_address).city, (home_address).country
FROM customers
WHERE customer_id = 1;

 name     | city   | country
---------+--------+---------
 Jordan Blake | London | UK

Domains

A domain is different from a composite type: it is not a new structure with multiple fields, it is a single existing base type with a name and, usually, a constraint attached. Once created, a domain can be used as a column type anywhere the base type would be used, and PostgreSQL automatically enforces the constraint on every column of that domain.

A positive_int domain

SQL

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

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  stock_qty  positive_int NOT NULL DEFAULT 0
);

INSERT INTO products (name, stock_qty) VALUES ('Wireless Mouse', -5);

ERROR:  value for domain positive_int violates check constraint "positive_int_check"

The real payoff of a domain shows up once the same constraint needs to apply to several columns across several tables — an email domain that requires a basic pattern match, or a currency_amount domain that forbids negative values, for example. Defining the rule once as a domain means every column that uses it stays consistent, and a future change to the rule only has to happen in one place.

A reusable email domain

SQL

CREATE DOMAIN email AS TEXT
  CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$');

ALTER TABLE customers
  ALTER COLUMN email TYPE email;

	Composite type	Domain
What it is	A structured type with multiple named fields	A single base type plus an optional constraint
Created with	CREATE TYPE ... AS (...)	CREATE DOMAIN ... AS base_type CHECK (...)
Typical use	Grouping related columns (address, money with currency)	Enforcing a rule everywhere a base type is used
Storage shape	Multiple values bundled together	Same storage as the underlying base type

Composite type vs. domain

These two are easy to confuse because both are created with a CREATE statement and both can be used as a column type. The distinction is what they represent: a composite type is structured — it has several fields you access individually. A domain is not structured at all — it is exactly one underlying type (say, INTEGER or TEXT) with a name and a constraint riding along with it, so it behaves as that base type everywhere while quietly enforcing the rule.

Dropping and altering

Use DROP TYPE for composite types and DROP DOMAIN for domains. Both refuse to drop if the type or domain is still in use by a table — you must remove or alter the dependent columns first. ALTER TYPE and ALTER DOMAIN can add fields or change constraints without a full drop-and-recreate in many cases.

Composite types complicate ALTER TABLE ... ADD COLUMN later

Adding a field to a composite type that is already used by existing tables can require careful handling — existing rows need a value for the new field. It is often simpler to add a plain new column to the tables that need it rather than evolving a widely used composite type after the fact.

CREATE TYPE ... AS (...) defines a composite type — a reusable, multi-field structure.
CREATE DOMAIN ... AS base_type CHECK (...) defines a named constraint layered on a single base type.
Domains enforce their constraint automatically on every column that uses them.
Use composite types to group related fields; use domains to centralize a validation rule.