How to list custom types using Postgres information_schema

SqlPostgresql

Sql Problem Overview


I am trying to find the equivalent SQL of \dT using the information_schema and can't seem to find anything. Does such a thing exist?

Example: If I add the following custom type enum, how can I see it in the information_schema?

CREATE TYPE communication.channels AS ENUM
   ('text_message',
    'email',
    'phone_call',
    'broadcast');

NOTE: I do have the exact SQL used by \dT (retrieved by turning up the logging) but I am looking specifically for a cleaner implementation using the information_schema

Sql Solutions


Solution 1 - Sql

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');

Solution 2 - Sql

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  +|       |                   |

Solution 3 - Sql

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;

Solution 4 - Sql

List of all defined by Your self types:

\dT

testDB=> \dT
               List of data types
 Schema |          Name           | Description 
--------+-------------------------+-------------
 public | myType                  | 
(1 row)

Solution 5 - Sql

Enums are not in the SQL standard and therefore not represented in the information schema. Other user-defined types would normally be in the view user_defined_types, but that's not implemented. So at the moment, you can't use the information schema to list user-defined types in PostgreSQL.

Solution 6 - Sql

I use a view to show my enum names. The data from that view can consequently be used in an application to provide a list of available options for an enum field.

CREATE OR REPLACE VIEW vw_enums AS
SELECT t.typname, e.enumlabel, e.enumsortorder
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid;

Solution 7 - Sql

Have a look here: http://www.postgresql.org/docs/current/static/catalog-pg-enum.html

The pg_enum catalogue should have the data you are looking fore

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionCollin PetersView Question on Stackoverflow
Solution 1 - SqlCollin PetersView Answer on Stackoverflow
Solution 2 - SqlVadim ShvetsovView Answer on Stackoverflow
Solution 3 - SqlGuidoView Answer on Stackoverflow
Solution 4 - SqlandrewView Answer on Stackoverflow
Solution 5 - SqlPeter EisentrautView Answer on Stackoverflow
Solution 6 - SqlStartupGuyView Answer on Stackoverflow
Solution 7 - SqlDrColossosView Answer on Stackoverflow