Ondřej Pospíšil Ondřej Pospíšil - 3 months ago 10
SQL Question

List data types from PostreSQL server

How to retrieve supported data types from PostgreSQL server? I need probably some select, which returns supported data types. Supported data types changes with every released version of Postgres.

Answer

I think you might be looking for something like this:

SELECT n.nspname, typname, pg_catalog.format_type(t.oid, NULL) AS typefull
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)

Let me know if it works for you.