How to list custom types using Postgres information_schema

Question

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

If you don't want to use the commandline, pgAdmin can do that — DrColossos, Sep 08 '10 at 16:57

score 78 · Answer 1 · edited Jan 13 '20 at 16:30

78

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

edited Jan 13 '20 at 16:30

andilabs

22,159
14
114
151

answered Sep 13 '10 at 19:32

Collin Peters

4,353
3
29
36

3

This code displays the list of all types, including user-defined enums, but does not give the details of how they are defined. – Eliyahu Skoczylas Jun 05 '17 at 14:05

score 74 · Answer 2 · answered Apr 07 '18 at 19:03

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

score 39 · Answer 3 · edited Sep 28 '12 at 14:11

39

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;

edited Sep 28 '12 at 14:11

Taryn

242,637
56
362
405

answered Aug 15 '12 at 11:34

Guido

867
9
13

1

The `AS enumlabel` part is redundant, don't you think? – Pere Feb 15 '17 at 15:38

score 14 · Answer 4 · answered Feb 23 '14 at 14:04

14

List of all defined by Your self types:

\dT

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

answered Feb 23 '14 at 14:04

andrew

3,083
4
24
29

score 11 · Accepted Answer · answered Jun 05 '11 at 20:06

11

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.

answered Jun 05 '11 at 20:06

Peter Eisentraut

35,221
12
85
90

17

please un-award this answer and give instead to "List all db types" `\dT` – WestCoastProjects Mar 27 '20 at 21:32

score 6 · Answer 6 · answered Apr 05 '13 at 17:18

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;

score 0 · Answer 7 · answered Sep 08 '10 at 17:03

0

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

answered Sep 08 '10 at 17:03

DrColossos

12,656
3
46
67

Unfortunately the pg_enum catalog only contains the enum label and not the schema or anything else. So you have to do a join anyways to get the meaningful data – Collin Peters Sep 13 '10 at 19:29

How to list custom types using Postgres information_schema

7 Answers7

Linked