I'd like to export the data types from a PostgreSQL specific schema. The problem is, for now I found just a way to export the whole schema and not just the data types. The schema has more than 2000 tables, and I don't need all this. Is there a way to export JUST the custom data types?
Add a comment
|
2 Answers
This gets you the list of available datatypes - for custom ones you probably need to filter by the first column (nspname):
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)
Comments
Not sure if this is what you had in mind or not:
select
data_type, count (*)
from information_schema.columns
where
table_schema = 'my_schema'
group by
data_type
If by custom types ONLY you mean user-defined, then I think you would want the udt_name instead for user-defined types:
select
udt_name, count (*)
from information_schema.columns
where
table_schema = 'my_schema' and
data_type = 'USER-DEFINED'
group by
udt_name
