How to list custom types using Postgres information_schema
This is a simple way to list all the enum defined types in the current database. The query result returns two columns, the first show the name of every enum types, the second show the name of every value for each enum type:
SELECT pg_type.typname AS enumtype,
pg_enum.enumlabel AS enumlabel
FROM pg_type
JOIN pg_enum
ON pg_enum.enumtypid = pg_type.oid;
List all db types:
test=# \dT
List of data types
Schema | Name | Description
--------+---------------------+-------------
public | gender |
public | status |
List all db types with additional information like values:
test=# \dT+
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description
--------+---------------------+---------------------+------+-------------------+-------+-------------------+-------------
public | gender | gender | 4 | male +| Vadim | |
| | | | female | | |
public | status | status | 4 | processing +| Vadim | |
| | | | passed +| | |
| | | | failed | | |
Get certain type with additional information:
leps=# \dT+ gender
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description
--------+------------+---------------+------+-------------------+-------+-------------------+-------------
public | gender | gender | 4 | male +| Vadim | |
| | | | female +| | |
For reference, here is the SQL from \dT (pgAdmin uses the same or similar)
SELECT n.nspname as schema, t.typname as type
FROM pg_type t
LEFT 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 NOT IN ('pg_catalog', 'information_schema');