如何通过查询获取Postgres中用户自定义types与domains的定义

以下查询全部基于系统目录表编写，输出结果为可直接执行的完整DDL语句，无需依赖pg_dump或第三方工具。

自定义TYPE对象DDL导出 #

支持枚举类型、复合类型、范围类型三类用户自定义TYPE的定义导出，输出统一为CREATE TYPE开头的可执行语句：

-- 导出全库用户自定义类型的完整DDL
WITH type_meta AS (
    SELECT 
        t.oid,
        n.nspname AS schema_name,
        t.typname AS type_name,
        t.typtype AS type_category
    FROM pg_type t
    JOIN pg_namespace n ON t.typnamespace = n.oid
    WHERE 
        -- 排除系统内置schema的对象
        n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
        -- 匹配用户自定义类型：e=枚举, c=复合, r=范围, m=多范围
        AND t.typtype IN ('e', 'c', 'r', 'm')
        -- 排除类型自动生成的关联数组类型
        AND t.typarray = 0
),
type_def AS (
    SELECT
        tm.oid,
        tm.schema_name,
        tm.type_name,
        tm.type_category,
        r.rngsubtype,
        r.rngcollation,
        r.rngsubopc,
        r.rngcanonical,
        r.rngsubdiff,
        -- 枚举类型DDL拼接
        CASE WHEN tm.type_category = 'e' THEN
            'CREATE TYPE ' || quote_ident(tm.schema_name) || '.' || quote_ident(tm.type_name) || ' AS ENUM (' ||
            (SELECT string_agg(quote_literal(enumlabel), ', ' ORDER BY enumsortorder)
             FROM pg_enum e WHERE e.enumtypid = tm.oid) || ');'
        -- 复合类型DDL拼接
        WHEN tm.type_category = 'c' THEN
            'CREATE TYPE ' || quote_ident(tm.schema_name) || '.' || quote_ident(tm.type_name) || ' AS (' ||
            (SELECT string_agg(quote_ident(attname) || ' ' || format_type(atttypid, atttypmod), ', ' ORDER BY attnum)
             FROM pg_attribute a 
             WHERE a.attrelid = tm.oid AND a.attnum > 0 AND NOT a.attisdropped) || ');'
        -- 范围类型DDL拼接
        WHEN tm.type_category IN ('r', 'm') THEN
            'CREATE TYPE ' || quote_ident(tm.schema_name) || '.' || quote_ident(tm.type_name) || ' AS RANGE (' ||
            'SUBTYPE = ' || format_type(r.rngsubtype, NULL) ||
            COALESCE(', COLLATION = ' || quote_ident(r.rngcollation::regcollation::text), '') ||
            COALESCE(', SUBTYPE_OPCLASS = ' || quote_ident(r.rngsubopc::regoperator::text), '') ||
            COALESCE(', CANONICAL = ' || quote_ident(r.rngcanonical::regproc::text), '') ||
            COALESCE(', SUBTYPE_DIFF = ' || quote_ident(r.rngsubdiff::regproc::text), '') ||
            ');'
        END AS ddl_stmt
    FROM type_meta tm
    LEFT JOIN pg_range r ON r.rngtypid = tm.oid
)
SELECT ddl_stmt FROM type_def WHERE ddl_stmt IS NOT NULL;

域（DOMAIN）对象DDL导出 #

支持域的基础类型、默认值、非空约束、自定义检查约束的全量定义导出：

-- 导出全库用户自定义域的完整DDL
SELECT
    'CREATE DOMAIN ' || quote_ident(n.nspname) || '.' || quote_ident(d.domainname) ||
    ' AS ' || format_type(d.dombasetype, d.domtypmod) ||
    -- 拼接默认值
    COALESCE(' DEFAULT ' || pg_get_expr(d.domdefault, d.dombasetypeoid), '') ||
    -- 拼接非空属性
    CASE WHEN d.domnotnull THEN ' NOT NULL' ELSE '' END ||
    -- 拼接所有域级检查约束
    COALESCE(
        (SELECT string_agg(', CONSTRAINT ' || quote_ident(con.conname) || ' CHECK (' || pg_get_expr(con.conbin, con.conrelid) || ')', '')
         FROM pg_constraint con
         WHERE con.contypid = d.oid),
        ''
    ) || ';' AS ddl_stmt
FROM pg_domain d
JOIN pg_namespace n ON d.domnamespace = n.oid
WHERE
    -- 排除系统内置schema的域
    n.nspname NOT IN ('pg_catalog', 'information_schema');

使用注意事项 #

• 所有输出语句都对对象名、字符串值做了转义处理，遇到保留字、含特殊字符的对象时可直接执行，不会出现语法冲突
• 语句自动携带对象所属的schema前缀，跨环境迁移时可按需调整前缀匹配目标库的schema结构
• 如果需要批量导出到文件，可在psql中执行查询时追加\g output.sql参数，将所有DDL直接写入本地文件

内容的提问来源于stack exchange，提问作者Mano