Display user-defined types and their details

Question

I've created a few new UDTs in PostgreSQL. However, now I have two problems:

1. how to see which UDTs have been defined?
2. how to see the columns defined within these UDTs?

Unfortunately, I couldn't find anything on that in the PostgreSQL documentation.

Answer 1

Does this get you started?

SELECT n.nspname AS schema,
        pg_catalog.format_type ( t.oid, NULL ) AS name,
        t.typname AS internal_name,
        CASE
            WHEN t.typrelid != 0
            THEN CAST ( 'tuple' AS pg_catalog.text )
            WHEN t.typlen < 0
            THEN CAST ( 'var' AS pg_catalog.text )
            ELSE CAST ( t.typlen AS pg_catalog.text )
        END AS size,
        pg_catalog.array_to_string (
            ARRAY( SELECT e.enumlabel
                    FROM pg_catalog.pg_enum e
                    WHERE e.enumtypid = t.oid
                    ORDER BY e.oid ), E'\n'
            ) AS elements,
        pg_catalog.obj_description ( t.oid, 'pg_type' ) AS description
    FROM pg_catalog.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 <> 'pg_catalog'
        AND n.nspname <> 'information_schema'
        AND pg_catalog.pg_type_is_visible ( t.oid )
    ORDER BY 1, 2;

In psql you can \set ECHO_HIDDEN on to make psql show you the queries used to generate the output of the \d... commands. I've found these queries to be very useful as a starting point when digging metadata out of databases.

Update: 2019-12-16

For composite types, the columns metadata can be determined using something like the following:

WITH types AS (
    SELECT n.nspname,
            pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
            CASE
                WHEN t.typrelid != 0 THEN CAST ( 'tuple' AS pg_catalog.text )
                WHEN t.typlen < 0 THEN CAST ( 'var' AS pg_catalog.text )
                ELSE CAST ( t.typlen AS pg_catalog.text )
                END AS obj_type,
            coalesce ( pg_catalog.obj_description ( t.oid, 'pg_type' ), '' ) AS description
        FROM pg_catalog.pg_type t
        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 <> 'pg_catalog'
            AND n.nspname <> 'information_schema'
            AND n.nspname !~ '^pg_toast'
),
cols AS (
    SELECT n.nspname::text AS schema_name,
            pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
            a.attname::text AS column_name,
            pg_catalog.format_type ( a.atttypid, a.atttypmod ) AS data_type,
            a.attnotnull AS is_required,
            a.attnum AS ordinal_position,
            pg_catalog.col_description ( a.attrelid, a.attnum ) AS description
        FROM pg_catalog.pg_attribute a
        JOIN pg_catalog.pg_type t
            ON a.attrelid = t.typrelid
        JOIN pg_catalog.pg_namespace n
            ON ( n.oid = t.typnamespace )
        JOIN types
            ON ( types.nspname = n.nspname
                AND types.obj_name = pg_catalog.format_type ( t.oid, NULL ) )
        WHERE a.attnum > 0
            AND NOT a.attisdropped
)
SELECT cols.schema_name,
        cols.obj_name,
        cols.column_name,
        cols.data_type,
        cols.ordinal_position,
        cols.is_required,
        coalesce ( cols.description, '' ) AS description
    FROM cols
    ORDER BY cols.schema_name,
            cols.obj_name,
            cols.ordinal_position ;

Answer 2

The default clients have functionality to cover that:

pgAdmin 4

Default GUI.

Make sure types are enabled in the object browser: File - Preferences - Browser - Nodes.

To the left you see user-defined types in the chosen schema. (Question 1)

The SQL pane to the right has the reverse engineered SQL script for the selected type. (Question 2)

More details in the other panes, like Dependents etc.

psql

The standard interactive console.

1. \dT to get a list of user-defined types.
2. \d type_name to get the column definition list for the given composite type. (Does nothing for other types.)

The manual:

\d[S+] [ pattern ]

For each relation (table, view, materialized view, index, sequence, or foreign table) or composite type matching the pattern, show all columns, their types, [...]

Bold emphasis mine. The command works for composite types as well since at least Postgres 9.1.

And:

\dT[S+] [ pattern ]

Lists data types. If pattern is specified, only types whose names match the pattern are listed. If + is appended to the command name, each type is listed with its internal name and size, its allowed values if it is an enum type, and its associated permissions. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects.

Answer 3

Try execute this code:

SELECT
    pg_type.typname, 
     pg_enum.enumlabel
FROM
    pg_type 
JOIN
    pg_enum ON pg_enum.enumtypid = pg_type.oid;

Answer 4

This is a very simple alternative, but enough for simple use cases. When the column's type is UDT, we use the udt_name column from information_schema.columns table.

select column_name, case 
        when (data_type = 'USER-DEFINED') then udt_name 
        else data_type 
    end as data_type
from information_schema.columns 
where table_schema = 'altimetria' and table_name = 'cnivel';

Result (geometryis a UDT):

 column_name  |    data_type     
--------------+------------------
 ogc_fid      | integer
 wkb_geometry | geometry
 id_cnivel    | bigint
 cod_cart     | bigint
 cod_fam      | bigint
 cod_sub      | integer
 z            | double precision
 shape_len    | double precision

Answer 5

With psql:

• \dT show list of user-defined types.
• \dT+ <type_name> show given user-defined type, with details.
• \dT <type_name> show given user-defined type, without details.
• \d <type_name> show the column definition of the given user-defined type
  NOTE: this is a plain \d, and describes the type in a similar fashion to a table

Answer 6

Using the anwser of gsiems, I've archived the goal to mimic the 'CREATE TYPE' in PgAdmin & PgBackup

WITH types AS (
    SELECT n.nspname,
            pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
            CASE
                WHEN t.typrelid != 0 THEN CAST ( 'tuple' AS pg_catalog.text )
                WHEN t.typlen < 0 THEN CAST ( 'var' AS pg_catalog.text )
                ELSE CAST ( t.typlen AS pg_catalog.text )
                END AS obj_type,
            coalesce ( pg_catalog.obj_description ( t.oid, 'pg_type' ), '' ) AS description
        FROM pg_catalog.pg_type t
        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 <> 'pg_catalog'
            AND n.nspname <> 'information_schema'
            AND n.nspname !~ '^pg_toast'
),
cols AS (
    SELECT n.nspname::text AS schema_name,
            pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
            a.attname::text AS column_name,
            pg_catalog.format_type ( a.atttypid, a.atttypmod ) AS data_type,
            a.attnotnull AS is_required,
            a.attnum AS ordinal_position,
            pg_catalog.col_description ( a.attrelid, a.attnum ) AS description
        FROM pg_catalog.pg_attribute a
        JOIN pg_catalog.pg_type t
            ON a.attrelid = t.typrelid
        JOIN pg_catalog.pg_namespace n
            ON ( n.oid = t.typnamespace )
        JOIN types
            ON ( types.nspname = n.nspname
                AND types.obj_name = pg_catalog.format_type ( t.oid, NULL ) )
        WHERE a.attnum > 0
            AND NOT a.attisdropped
)
SELECT 'CREATE TYPE ' || cols.schema_name || '.' || cols.obj_name || E' AS (\n    ' ||
    pg_catalog.array_to_string (ARRAY( 
        SELECT cols.column_name || ' ' || cols.data_type AS col_num_typ
        FROM cols
        WHERE cols.obj_name='my_user_datatype'
        ORDER BY cols.schema_name,
                cols.obj_name,
                cols.ordinal_position ), E',\n    '
        ) || E'\n);'
    AS cre_typ
FROM cols
WHERE cols.obj_name='my_user_datatype'
LIMIT 1

and run it under psql with this commands to have only the SQL code \t\a\g\a\t.

Ok in PG 9.6. Say me if it's running with yours instances. Obviously, it need to be optimised and tested.

Answer 7

• Way 1:

SELECT typname, typcategory
FROM pg_type
WHERE typname='custom_type_name';

• Way 2:

SELECT column_name, data_type, udt_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name ='your_table_name';