CODEWITHSUNDEEP
sqlpostgresql
Collin Peters
Collin Peters

Reputation: 4643

How to list custom types using Postgres information_schema

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

Upvotes: 74

Views: 81581

Answers (7)

Collin Peters
Collin Peters

Reputation: 4643

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

Upvotes: 85

Vadim Shvetsov
Vadim Shvetsov

Reputation: 2376

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

Upvotes: 89

andrew
andrew

Reputation: 3141

List of all defined by Your self types:

\dT

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

Upvotes: 15

Michael M
Michael M

Reputation: 8753

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;

Upvotes: 7

Guido
Guido

Reputation: 887

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;

Upvotes: 41

Peter Eisentraut
Peter Eisentraut

Reputation: 36729

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.

Upvotes: 12

DrColossos
DrColossos

Reputation: 12998

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

Upvotes: 0

Related Questions