CODEWITHSUNDEEP
postgresqlddl
Izbassar Tolegen
Izbassar Tolegen

Reputation: 2152

Find definition of type in PostgreSQL

I have such DDL of tables from PostgreSQL:

CREATE TABLE some_table (
    id identifier NOT NULL DEFAULT nextval('some_table_id_seq'),
    center_name common_name NULL,
    ...
)

Those identifier and common_name types are not standart PostgreSQL types. How to find their definitions?

Upvotes: 1

Views: 684

Answers (1)

Anuraag Veerapaneni
Anuraag Veerapaneni

Reputation: 679

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 n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2;

Upvotes: 1

Related Questions