有没有一种方法可以显示用户定义的postgresql枚举类型定义？

Question

假设我们定义了一个PostgreSQL类型：

CREATE TYPE my_type AS ENUM('foo', 'bar');

有没有办法在创建后显示类型定义？

我期望 "\d my_type" 显示 "ENUM('foo', 'bar')"，但它显示的是：

Did not find any relation named "my_type"

pg_type表似乎没有提供足够的信息。

- Stéphane

Answer 1

请查看这个：

select enum_range(null::my_type)

我认为这是一个更简单的解决方案 :).

- Adam111p

---

我喜欢这个的简洁性。顶级作品无疑是好的......但这个也不错。 :) - PRS

我喜欢这个。在dbeaver或pgadmin中\d语法不起作用，但在可移植的纯SQL中可以。 - Darren

适用于PostgreSQL 8.3.11。 - Yzmir Ramirez

如果我的表名为“users”，列名为“status”，枚举类型为“enum_user_status”，那么在null:my_type中我应该输入什么？我尝试了所有我能想到的组合，但都没有成功。 - exebook

我已经晚了很久，但我使用了null::enum_user_status（显然，我使用的是我的枚举名称而不是enum_user_status）。使用枚举的表/列没有涉及。 - dano

Answer 2

你需要的是 \dT，但它并不以“CREATE”语句形式呈现。你应该使用 \dD 来创建域。

\dT+ action.action_status
                          List of data types
 Schema |         Name         | Internal name | Size | Elements | Description 
--------+----------------------+---------------+------+----------+-------------
 action | action.action_status | action_status | 4    | pending +| 
        |                      |               |      | live    +| 
        |                      |               |      | done    +| 
        |                      |               |      | notdone  | 
(1 row)

- Richard Huxton

---

我不理解“您使用\dD用于域名”的相关性。 - Faheem Mitha

域和类型是不同的概念。使用 \dD 表示域，使用 \dT 表示类型。 - Richard Huxton

好的，我知道了。 "域本质上是一种带有可选约束条件（限制允许值的集合）的数据类型。" - Faheem Mitha

我花了一些时间才意识到我真正想要的是 \dT+ my_enum;。 - gylaz

Answer 3

手册：

enum_range ( anyenum ) → anyarray

返回有序数组中输入枚举类型的所有值。

还可以获取模式：

SELECT typnamespace::regnamespace AS "schema", typname
     , enum_range(null::my_enum_type)
FROM   pg_catalog.pg_type 
WHERE  typname = 'my_enum_type';

返回：

 schema | typname      | enum_labels
--------+--------------+-------------
 public | my_enum_type | {foo,bar}

您还可以从转储文件中获取SQL CREATE语句。最便宜的方式是使用仅模式的转储，使用pg_dump --schema-only ...命令。

或者简单地使用pgAdmin。在对象浏览器中选择对象，其创建脚本将显示在SQL窗格中。

- Erwin Brandstetter

---

请注意，您需要在 pgAdmin 中启用查看类型的默认设置：文件 → 选项 → 浏览器，并勾选 类型。 - Eugene Yarmash

实际上，在1.18.1版本中，你可以在“文件”->“选项”->“显示”中找到该选项。 - Mohamad mehdi Kharatizadeh

Answer 4

SELECT t.typname
FROM pg_class c JOIN pg_attribute a ON c.oid = a.attrelid JOIN pg_type t ON a.atttypid = t.oid
WHERE c.relname = 'your_type';

棘手的部分在于仅从这些视图中选择*并不能在结果中获得OIDs。

- dezso

Answer 5

使用这篇文章，我已经实现了在PgAdmin和PgBackup中模拟“CREATE TYPE”的目标。

WITH types AS (
    SELECT n.nspname,
            pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
            CASE
                WHEN t.typrelid != 0 THEN CAST ( 'tuple' AS pg_catalog.text )
                WHEN t.typlen < 0 THEN CAST ( 'var' AS pg_catalog.text )
                ELSE CAST ( t.typlen AS pg_catalog.text )
                END AS obj_type,
            coalesce ( pg_catalog.obj_description ( t.oid, 'pg_type' ), '' ) AS description
        FROM pg_catalog.pg_type t
        JOIN pg_catalog.pg_namespace n
            ON n.oid = t.typnamespace
        WHERE ( t.typrelid = 0
                OR ( SELECT c.relkind = 'c'
                        FROM pg_catalog.pg_class c
                        WHERE c.oid = t.typrelid ) )
            AND NOT EXISTS (
                    SELECT 1
                        FROM pg_catalog.pg_type el
                        WHERE el.oid = t.typelem
                        AND el.typarray = t.oid )
            AND n.nspname <> 'pg_catalog'
            AND n.nspname <> 'information_schema'
            AND n.nspname !~ '^pg_toast'
),
cols AS (
    SELECT n.nspname::text AS schema_name,
            pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
            a.attname::text AS column_name,
            pg_catalog.format_type ( a.atttypid, a.atttypmod ) AS data_type,
            a.attnotnull AS is_required,
            a.attnum AS ordinal_position,
            pg_catalog.col_description ( a.attrelid, a.attnum ) AS description
        FROM pg_catalog.pg_attribute a
        JOIN pg_catalog.pg_type t
            ON a.attrelid = t.typrelid
        JOIN pg_catalog.pg_namespace n
            ON ( n.oid = t.typnamespace )
        JOIN types
            ON ( types.nspname = n.nspname
                AND types.obj_name = pg_catalog.format_type ( t.oid, NULL ) )
        WHERE a.attnum > 0
            AND NOT a.attisdropped
)
SELECT 'CREATE TYPE ' || cols.schema_name || '.' || cols.obj_name || E' AS (\n    ' ||
pg_catalog.array_to_string (ARRAY( 
    SELECT cols.column_name || ' ' || cols.data_type AS col_num_typ
    FROM cols
    WHERE cols.obj_name='my_user_data_type'
    ORDER BY cols.schema_name,
            cols.obj_name,
            cols.ordinal_position ), E',\n    '
    ) || E'\n);'
AS cre_typ
FROM cols
WHERE cols.obj_name='my_user_data_type'
LIMIT 1

使用以下命令在 psql 下运行，以仅获取 SQL 代码：

\t\a\g\a\t

- Pct Mtnxt