37

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.

7 Answers 7

38

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 ;
2
  • 1
    I'd upvote twice if I could for you bonus info of \set ECHO_HIDDEN on. (I mention this here as a comment specifically because it can be easily overlooked in skimming through your answer)
    – Randall
    Feb 9, 2023 at 21:25
  • I don't even want to know how you came up with this query, but thanks a bunch!
    – tftd
    May 9 at 8:29
31

The default clients have functionality to cover that:

pgAdmin 4

Default GUI.

types in pgAdmin4

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.

3
  • 3
    JetBrains IDEs have a database plugin (also packaged as a stand-alone product called DataGrip) that displays user defined types in a similar fashion what is shown here with PGAdmin. Aug 16, 2020 at 14:58
  • \d <type_name> does not get you the description of the type, it gets you "Did not find any relation named <type_name>"
    – philosodad
    Jan 12, 2023 at 20:07
  • 1
    @philosodad: \d typename is only useful for composite types (which this question is mainly about). I clarified some more above. (Though I had stressed that fact with bold emphasis before.) Jan 14, 2023 at 7:00
12

Try execute this code:

SELECT
    pg_type.typname, 
     pg_enum.enumlabel
FROM
    pg_type 
JOIN
    pg_enum ON pg_enum.enumtypid = pg_type.oid;
2
  • 2
    I got no results from this query. Not sure what the data was supposed to look like. Jun 25, 2019 at 18:51
  • This query will only return user-defined enums. There are other user-defined types (such as composite types) that will not be returned by this query.
    – bitoffdev
    Oct 7, 2021 at 13:24
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
4

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
4
  • 4
    This adds nothing to existing answers, I'm afraid.
    – mustaccio
    Oct 27, 2021 at 12:07
  • 1
    Thank you! This is the only answer that includes the \dT+ command which is exactly what I needed Dec 21, 2022 at 20:44
  • 1
    This answer does add something. No other answer contains \dT+ <type_name>, which actually gets you the description of the type.
    – philosodad
    Jan 12, 2023 at 20:10
  • 1
    The one thing missing from this answer was the plain \d that gives the column definition of a UDT - that solves the OP's problem #2. I've added it to the answer. And, that's the one I always forget! And I like this answer, because it's short & sweet for psql
    – Randall
    Nov 24, 2023 at 18:36
-1

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.

-3
  • 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';
1
  • 2
    Welcome to dba stackexchange Somehow I am not convinced that your answer brings any value to this question. Have you seen the other answers? Nov 13, 2022 at 7:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.