PostgreSQL: How to list all available datatypes?

Question

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.

Answer 1

"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;
**************************