In PostgreSQL, domains are basically data types with optional constraints. We can create them as a kind of user-defined data type, and then reuse them in our columns going forward.

As with any user-created object, we sometimes need to see a list of existing domains in a PostgreSQL database. Listing all domains can be useful for database management, documentation, or troubleshooting.

This article will show you how to retrieve a list of all domains in your PostgreSQL database.

Using psql

If you’re using the psql command-line interface, you can use the \dD command:

\dD

Example result:

 Schema |     Name      |          Type          | Collation | Nullable | Default |                                                      Check                                                      
--------+---------------+------------------------+-----------+----------+---------+-----------------------------------------------------------------------------------------------------------------
 public | age_years     | integer                |           |          |         | CHECK (VALUE >= 0 AND VALUE <= 150)
 public | email_address | character varying(255) |           |          |         | CHECK (VALUE::text ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'::text) CHECK (length(VALUE::text) >= 5)
 public | money_usd     | numeric(10,2)          |           |          | 0.00    | CHECK (VALUE >= 0::numeric)
(3 rows)

This command returns a lot of useful information about the domains in the current database, including base type, the CHECK constraints, default value, etc.

SQL Query

If you’re not using psql, you can run the following query:

SELECT 
    n.nspname AS schema,
    t.typname AS name,
    pg_catalog.format_type(t.typbasetype, t.typtypmod) AS base_type,
    (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt
     WHERE c.oid = t.typcollation AND bt.oid = t.typbasetype AND t.typcollation <> bt.typcollation) AS collation,
    t.typnotnull AS not_null,
    t.typdefault AS default,
    pg_catalog.array_to_string(ARRAY(
        SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r 
        WHERE t.oid = r.contypid
    ), ' ') AS check
FROM pg_catalog.pg_type t
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE t.typtype = 'd'
ORDER BY 1, 2;

Example result:

       schema       |      name       |          base_type          | collation | not_null |       default        |                                                      check                                                      
--------------------+-----------------+-----------------------------+-----------+----------+----------------------+-----------------------------------------------------------------------------------------------------------------
 information_schema | cardinal_number | integer                     |           | f        |                      | CHECK (VALUE >= 0)
 information_schema | character_data  | character varying           | C         | f        |                      | 
 information_schema | sql_identifier  | name                        |           | f        |                      | 
 information_schema | time_stamp      | timestamp(2) with time zone |           | f        | CURRENT_TIMESTAMP(2) | 
 information_schema | yes_or_no       | character varying(3)        | C         | f        |                      | CHECK (VALUE::text = ANY (ARRAY['YES'::character varying, 'NO'::character varying]::text[]))
 public             | age_years       | integer                     |           | f        |                      | CHECK (VALUE >= 0 AND VALUE <= 150)
 public             | email_address   | character varying(255)      |           | f        |                      | CHECK (VALUE::text ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'::text) CHECK (length(VALUE::text) >= 5)
 public             | money_usd       | numeric(10,2)               |           | f        | 0.00                 | CHECK (VALUE >= 0::numeric)
(8 rows)

This time we also got the information schema domains. Every column in the information schema has one of these five types. The PostgreSQL documentation states:

You should not use these types for work outside the information schema, but your applications must be prepared for them if they select from the information schema.

The following query excludes information schema domains:

SELECT 
    n.nspname AS schema_name,
    t.typname AS domain_name,
    pg_catalog.format_type(t.typbasetype, t.typtypmod) AS base_type,
    t.typnotnull AS not_null,
    t.typdefault AS default_value,
    pg_catalog.array_to_string(ARRAY(
        SELECT pg_catalog.pg_get_constraintdef(r.oid, true)
        FROM pg_catalog.pg_constraint r
        WHERE t.oid = r.contypid
    ), ' AND ') AS check_constraints,
    d.description AS comment
FROM pg_catalog.pg_type t
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
LEFT JOIN pg_catalog.pg_description d ON d.objoid = t.oid AND d.classoid = 'pg_type'::regclass
WHERE t.typtype = 'd'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schema_name, domain_name;

Result:

 schema_name |  domain_name  |       base_type        | not_null | default_value |                                                  check_constraints                                                  | comment 
-------------+---------------+------------------------+----------+---------------+---------------------------------------------------------------------------------------------------------------------+---------
 public      | age_years     | integer                | f        |               | CHECK (VALUE >= 0 AND VALUE <= 150)                                                                                 | 
 public      | email_address | character varying(255) | f        |               | CHECK (VALUE::text ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'::text) AND CHECK (length(VALUE::text) >= 5) | 
 public      | money_usd     | numeric(10,2)          | f        | 0.00          | CHECK (VALUE >= 0::numeric)                                                                                         | 
(3 rows)

This query provides the following information for each domain:

1. schema_name: The schema in which the domain is defined.
2. domain_name: The name of the domain.
3. base_type: The underlying data type of the domain.
4. not_null: Indicates whether the domain has a NOT NULL constraint.
5. default_value: The default value for the domain, if any.
6. check_constraints: Any CHECK constraints defined for the domain.
7. comment: Any comment associated with the domain.

Here’s a breakdown of what the query does:

1. It selects from pg_type, which contains information about data types, including domains.
2. It joins with pg_namespace to get the schema name.
3. It left joins with pg_description to get any comments associated with the domain.
4. The subquery within array_to_string retrieves any CHECK constraints associated with the domain from pg_constraint.
5. The WHERE clause filters for domain types (typtype = 'd') and excludes system schemas.
6. The results are ordered by schema name and domain name.