假设我们定义了一个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表似乎没有提供足够的信息。
假设我们定义了一个PostgreSQL类型:
CREATE TYPE my_type AS ENUM('foo', 'bar');
有没有办法在创建后显示类型定义?Did not find any relation named "my_type"
pg_type表似乎没有提供足够的信息。
请查看这个:
select enum_range(null::my_type)
我认为这是一个更简单的解决方案 :).
你需要的是 \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)
\dT+ my_enum;
。 - gylaz
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窗格
中。
pgAdmin
中启用查看类型的默认设置:文件 → 选项 → 浏览器,并勾选 类型
。 - Eugene YarmashSELECT 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。
使用这篇文章,我已经实现了在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
null:my_type
中我应该输入什么?我尝试了所有我能想到的组合,但都没有成功。 - exebooknull::enum_user_status
(显然,我使用的是我的枚举名称而不是enum_user_status
)。使用枚举的表/列没有涉及。 - dano