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
– DrColossos, Commented Sep 8, 2010 at 16:57

Vadim Shvetsov · Accepted Answer · 2018-04-07 19:03:36Z

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

andilabs · Accepted Answer · 2020-01-13 16:30:24Z

86

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, 2020 at 16:30

andilabs

23.5k14 gold badges126 silver badges159 bronze badges

answered Sep 13, 2010 at 19:32

Collin Peters

4,7334 gold badges34 silver badges37 bronze badges

1 Comment

Eliyahu Skoczylas Over a year ago

This code displays the list of all types, including user-defined enums, but does not give the details of how they are defined.

Taryn · Accepted Answer · 2012-09-28 14:11:13Z

43

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, 2012 at 14:11

Taryn

249k57 gold badges375 silver badges409 bronze badges

answered Aug 15, 2012 at 11:34

Guido

90710 silver badges13 bronze badges

1 Comment

Pere Over a year ago

The AS enumlabel part is redundant, don't you think?

andrew · Accepted Answer · 2014-02-23 14:04:11Z

15

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, 2014 at 14:04

andrew

3,1514 gold badges27 silver badges30 bronze badges

Comments

Peter Eisentraut · Accepted Answer · 2011-06-05 20:06:40Z

12

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 5, 2011 at 20:06

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

2 Comments

WestCoastProjects Over a year ago

please un-award this answer and give instead to "List all db types" \dT

Demurgos Over a year ago

The original question asks about the information_schema specifically. This answer here is the correct one. Other answers mentioning \dT may be useful but they are also irrelevant to the question.

Michael M · Accepted Answer · 2013-04-05 17:18:19Z

7

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;

answered Apr 5, 2013 at 17:18

Michael M

9,0032 gold badges39 silver badges55 bronze badges

Comments

DrColossos · Accepted Answer · 2010-09-08 17:03:13Z

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 8, 2010 at 17:03

DrColossos

13.1k3 gold badges49 silver badges68 bronze badges

1 Comment

Collin Peters Over a year ago

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

Collectives™ on Stack Overflow

How to list custom types using Postgres information_schema

7 Answers 7

Comments

1 Comment

1 Comment

Comments

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

1 Comment

1 Comment

Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related