Is there a way to show a user-defined postgresql enumerated type definition?
PostgresqlEnumsPostgresql Problem Overview
Let's say we've defined a postgresql type:
CREATE TYPE my_type AS ENUM('foo', 'bar');
Is there any way to show the type definition after creation ?
I would expect "\d my_type" to show me "ENUM('foo', 'bar')", but it says :
Did not find any relation named "my_type"
The pg_type table doesn't seem to give enough information.
Postgresql Solutions
Solution 1 - Postgresql
Check this:
select enum_range(null::my_type)
I think this is a much simpler solution :).
Solution 2 - Postgresql
It's \dT you're after, but it doesn't give it as a "CREATE" statement. You use \dD for domains.
\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)
Solution 3 - Postgresql
If you just want the full name (type name and schema) and a sorted list of all enum
labels, this query will do:
SELECT n.nspname AS "schema", t.typname
, string_agg(e.enumlabel, '|' ORDER BY e.enumsortorder) AS enum_labels
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
JOIN pg_catalog.pg_enum e ON t.oid = e.enumtypid
WHERE t.typname = 'my_enum_type'
GROUP BY 1,2;
Returns:
schema | typname | enum_labels
--------+--------------+-------------
public | my_enum_type | foo|bar
string_agg()
requires Postgres 9.0 or later, replace with array_agg()
for older versions.
To get the SQL CREATE
statement, you could use pg_dump
and look at the dump file.
Or, much more practically, use pgAdmin which displays reverse engineered SQL create scripts for any object in the database. Select it in the object browser
and its create script is displayed in the SQL pane
. There is even an option to copy the script to a newly opened window of the SQL editor
automatically, where you can edit and execute it.
Solution 4 - Postgresql
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';
The tricky part was that simply selecting * from these views one does not get OIDs in the results.
Solution 5 - Postgresql
using this post, I've archived the goal to mimic the 'CREATE TYPE' in PgAdmin & PgBackup
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
and run it under psql with this command to have only the SQL code :
\t\a\g\a\t