How to List Custom Types Using Postgres Information_Schema

How to list custom types using Postgres information_schema

Enums are not in the SQL standard and therefore not represented in the information schema. Other user-defined types would normally be in the view user_defined_types, but that's not implemented. So at the moment, you can't use the information schema to list user-defined types in PostgreSQL.

How to get a list column names and datatypes of a table in PostgreSQL?

SELECT
        a.attname as "Column",
        pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
    FROM
        pg_catalog.pg_attribute a
    WHERE
        a.attnum > 0
        AND NOT a.attisdropped
        AND a.attrelid = (
            SELECT c.oid
            FROM pg_catalog.pg_class c
                LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relname ~ '^(hello world)$'
                AND pg_catalog.pg_table_is_visible(c.oid)
        );

More info on it : http://www.postgresql.org/docs/9.3/static/catalog-pg-attribute.html

List all tables in postgresql information_schema

You should be able to just run select * from information_schema.tables to get a listing of every table being managed by Postgres for a particular database.

You can also add a where table_schema = 'information_schema' to see just the tables in the information schema.

How to list PostgreSQL columns (with name, type and description) by schema and table?

is this you are looking for?

pgd.description is column comment

SELECT
 c.ordinal_position,
 c.table_schema,
 c.table_name,
 c.column_name,
 pgd.description,
 c.data_type,
 c.udt_name as udt_name

FROM pg_catalog.pg_statio_all_tables as st

LEFT JOIN information_schema.columns as c
ON c.table_schema=st.schemaname and c.table_name=st.relname

LEFT JOIN pg_catalog.pg_description as pgd
ON pgd.objsubid=c.ordinal_position and  pgd.objoid=st.relid

WHERE 
 st.schemaname NOT IN ('pg_catalog', 'information_schema')

list Postgres ENUM type

select n.nspname as enum_schema,  
       t.typname as enum_name,  
       e.enumlabel as enum_value
from pg_type t 
   join pg_enum e on t.oid = e.enumtypid  
   join pg_catalog.pg_namespace n ON n.oid = t.typnamespace;

SQL, Get a list of all the custom data types

Oracle has a data dictionary view for almost everything. You can select from
ALL_TYPES and ALL_TYPE_ATTRS. The latter shows the owner of each type.

Postgres does not have the same functionality so you can use this query from here

SELECT      n.nspname as schema, t.typname as type 
FROM        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 NOT IN ('pg_catalog', 'information_schema')

How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?

After some searching, I was able to find the information_schema.routines table and the information_schema.parameters tables. Using those, one can construct a query for this purpose. LEFT JOIN, instead of JOIN, is necessary to retrieve functions without parameters.

SELECT routines.routine_name, parameters.data_type, parameters.ordinal_position
FROM information_schema.routines
    LEFT JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name
WHERE routines.specific_schema='my_specified_schema_name'
ORDER BY routines.routine_name, parameters.ordinal_position;

PostgreSQL: find information about user defined types

The catalog pg_type stores information about data types. Base types and enum types (scalar types) are created with CREATE TYPE, and domains with CREATE DOMAIN.

More information about pg_type plz visit
http://www.postgresql.org/docs/9.0/static/catalog-pg-type.html

How can I query for the definitions of all domains in the database?

This returns what you ask for, plus some more columns that may be relevant:

SELECT n.nspname AS schema
     , t.typname AS name
     , pg_catalog.format_type(t.typbasetype, t.typtypmod) AS underlying_type
     , t.typnotnull AS not_null
       
     , (SELECT c.collname
        FROM   pg_catalog.pg_collation c, pg_catalog.pg_type bt
        WHERE  c.oid = t.typcollation AND bt.oid = t.typbasetype AND t.typcollation <> bt.typcollation) AS collation
     , t.typdefault AS default
     , pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.pg_get_constraintdef(r.oid, TRUE) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid), ' ') AS check_constraints
FROM   pg_catalog.pg_type t
LEFT   JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE  t.typtype = 'd'  -- domains
AND    n.nspname <> 'pg_catalog'
AND    n.nspname <> 'information_schema'
AND    pg_catalog.pg_type_is_visible(t.oid)
ORDER  BY 1, 2;

db<>fiddle here

To get every domain type in a Postgres database remove the added filters:

AND    n.nspname <> 'pg_catalog'
AND    n.nspname <> 'information_schema'
AND    pg_catalog.pg_type_is_visible(t.oid)

But you'll probably just want visible user-domains.

Read the manual about pg_type.

In psql use \dD. And if you do that after setting \set ECHO_HIDDEN on, you'll also see the query used to generate that output. You'll find similarities to my query above (hint!).

---

Related Topics