Thread: SQL equivalent to \dT

Hi all,

if I want to get a list of types (ie., data types or enums), then I can
use the '\dT' command from within the postgreSQL client.

However, I cannot seem to figure out what the SQL alternative is to the
\dT command, so that I might get a list of types scriptable by SQL.

For example, if I create an ENUM myself:
CREATE TYPE bird AS ENUM('duck','goose');

a quick look through the various parts of the information schema did not
reveal in which place this enum is stored. Is the information schema the
correct place to look for this? Which SQL statement do I need to get a
list of user-defined types?

thanks in advance,

Bram Kuijper

Hello

2008/9/2 Bram Kuijper <a.l.w.kuijper@rug.nl>:
> Hi all,
>
> if I want to get a list of types (ie., data types or enums), then I can use
> the '\dT' command from within the postgreSQL client.
>

run psql with -E parameter. You will see all SQL statements used for
metacommands.

[pavel@localhost ~]$ psql -E postgres
psql (8.4devel)
Type "help" for help.

postgres=# \dT
********* QUERY **********
SELECT n.nspname as "Schema",
  pg_catalog.format_type(t.oid, NULL) AS "Name",
  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 pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2;
**************************

                                              List of data types
   Schema   |            Name             |
Description
------------+-----------------------------+-------------------------------------------------------------------
 pg_catalog | abstime                     | absolute, limited-range
date and time (Unix system time)
 pg_catalog | aclitem                     | access control list
 pg_catalog | "any"                       |
 pg_catalog | anyarray                    |
 pg_catalog | anyelement                  |

regards
Pavel Stehule

> However, I cannot seem to figure out what the SQL alternative is to the \dT
> command, so that I might get a list of types scriptable by SQL.
>
> For example, if I create an ENUM myself:
> CREATE TYPE bird AS ENUM('duck','goose');
>
> a quick look through the various parts of the information schema did not
> reveal in which place this enum is stored. Is the information schema the
> correct place to look for this? Which SQL statement do I need to get a list
> of user-defined types?
>
> thanks in advance,
>
> Bram Kuijper
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>