CODEWITHSUNDEEP
postgresqldump
Alexander Rumanovsk
Alexander Rumanovsk

Reputation: 2303

Export data types from PostgreSQL schema

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?

Upvotes: 1

Views: 2888

Answers (2)

Hambone
Hambone

Reputation: 16377

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

Upvotes: 1

Boris Schegolev
Boris Schegolev

Reputation: 3701

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)

Upvotes: 2

Related Questions