12

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.

17

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 ;
| improve this answer | |
  • 3
    This tip with ECHO_HIDDEN is priceless. – dezso Feb 26 '13 at 21:25
  • 1
    @dezso: You get the same with psql -E. – Erwin Brandstetter Feb 27 '13 at 0:12
  • Thanks a lot for the code and the explanations. Just one thing: what do you mean by ´\d...´ commands? – navige Feb 27 '13 at 8:35
  • 1
    Type \? in psql or read the manual here to get a list of meta-commands plus explanation. \d (& friends) describe objects of the database. Internally, most meta-commands are executed as SQL against the system catalog, which can also displayed with the mentioned options. – Erwin Brandstetter Feb 27 '13 at 14:25
  • 1
    Nice, but it only shows one line per type, just like \dT+ in psql. What about the information on how to create the type, just like pgAdmin shows it (see next answer), but without using pgAdmin? – Holger Jakobs Mar 28 '17 at 10:43
13

You can use the standard GUI pgAdmin:

types in pgAdmin

Make sure types are enabled in the object browser (Options - Browser - Display)

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 pane above that, like Dependents etc.


Alternatively, use the standard interactive console psql:

  1. \dT to get a list of user-defined types.
  2. \d type_name to get the column definition list for the given type.

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.

| improve this answer | |
  • 1
    How to get this information without using pgAdmin? – Holger Jakobs Mar 28 '17 at 10:44
  • 1
    @HolgerJakobs: I added an alternative with psql above. – Erwin Brandstetter Mar 28 '17 at 12:31
  • 1
    \dT gives a list of types, \dT type_name doesn't give the columns, \d type_name shows "did not find any relation named "...". If the type is there due to a table of the same name, it works, but a type created by "CREATE TYPE" doesn't show its columns. – Holger Jakobs Mar 29 '17 at 13:20
  • @HolgerJakobs: I know this works as advertised, and I tested before posting. That is, in modern-day Postgres (psql, to be precise). You must be using an outdated version of psql. See added bits above. – Erwin Brandstetter Mar 30 '17 at 1:58
  • 3
    i got the same message, but \dT+ type_name worked. (version 9.5) – Spike Jul 6 '17 at 4:15
1

Try execute this code:

SELECT
    pg_type.typname, 
     pg_enum.enumlabel
FROM
    pg_type 
JOIN
    pg_enum ON pg_enum.enumtypid = pg_type.oid;
| improve this answer | |
  • 1
    More explanation is generally considered appropriate for why code should be used. – James Jenkins Jun 4 '19 at 12:12
  • I got no results from this query. Not sure what the data was supposed to look like. – jDub9 Jun 25 '19 at 18:51
1

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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