PostgreSQL: How to list all available datatypes?

postgresql, postgresql-9.1, postgresql-9.2, sql

Solution

"data types" in PostgreSQL actually includes primitive (built-in) types, types added by extensions, user-defined composite types, domains, and table rowtypes. It isn't clear which of these are of interest to you. All types available in a given database are listed in that database's `pg_catalog.pg_type` so you may need to filter the results. See the documentation for the `pg_type` system catalog table.

Types for available but not installed extensions are not listed. There's no way to list types provided by extensions not installed in the current database.

To get a prettier listing of types use `psql`'s `\dT *` command. You can see the underlying SQL this executes by running `psql` with the `-E` flag:

$ psql -E regress
regress=> \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;
**************************

Problem

Question: In PostgreSQL (using SQL, not the console), how can I list all available datataypes ? Ideally like this: http://www.java2s.com/Code/PostgreSQL/Postgre-SQL/Displaysalldatatypesintheconnecteddatabasewithcomments.htm It should also list user defined types, if there are any. Just like the list in pgAdmin3 where you define the datatype for a new column in a table.

Original source