Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL: How to list all available datatypes?

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.

like image 351
Stefan Steiger Avatar asked May 02 '13 16:05

Stefan Steiger


2 Answers

"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;
**************************
like image 111
Craig Ringer Avatar answered Nov 20 '22 00:11

Craig Ringer


select * from pg_type;

pg_type

like image 18
Clodoaldo Neto Avatar answered Nov 20 '22 00:11

Clodoaldo Neto