Is there a way to show a user-defined postgresql enumerated type definition?

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.

Solution 1:

Check this:

select enum_range(null::my_type)

I think this is a much simpler solution :).

Solution 2:

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:

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.