How can I query for the definitions of all domains in the database?

Question

I'm trying to find a way to run a SELECT query (not some admin command) that returns the definition for every domain type in a Postgres database. Specifically, I'd like to know:

Schema
Name
Underlying type
Null/not null

Trying to Google this is tricky, because searching for information about querying the definitions of custom types in Postgres gives tons of results about enums for some reason, but nothing useful about domains. Does anyone know how to retrieve domain definitions from Postgres metadata?

See here [Types](https://www.postgresql.org/docs/12/catalog-pg-type.html) in particular column `typtype`. — Adrian Klaver, Jul 04 '21 at 23:22
@AdrianKlaver It looks like `typtype` will tell me that "this is a domain," which is useful, but it won't tell me "this is a domain representing a `varchar(20) not null`," which is what I'm trying to figure out. — Mason Wheeler, Jul 04 '21 at 23:37

score 2 · Accepted Answer · answered Jul 05 '21 at 00:14

This returns what you ask for, plus some more columns that may be relevant:

SELECT n.nspname AS schema
     , t.typname AS name
     , pg_catalog.format_type(t.typbasetype, t.typtypmod) AS underlying_type
     , t.typnotnull AS not_null
       
     , (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.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_constraints
FROM   pg_catalog.pg_type t
LEFT   JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE  t.typtype = 'd'  -- domains
AND    n.nspname <> 'pg_catalog'
AND    n.nspname <> 'information_schema'
AND    pg_catalog.pg_type_is_visible(t.oid)
ORDER  BY 1, 2;

db<>fiddle here

To get every domain type in a Postgres database remove the added filters:

AND    n.nspname <> 'pg_catalog'
AND    n.nspname <> 'information_schema'
AND    pg_catalog.pg_type_is_visible(t.oid)

But you'll probably just want visible user-domains.

Read the manual about pg_type.

In psql use \dD. And if you do that after setting \set ECHO_HIDDEN on, you'll also see the query used to generate that output. You'll find similarities to my query above (hint!).

How can I query for the definitions of all domains in the database?

1 Answers1