Equivalent view of "user_types" (ORACLE) in POSTGRESQL?-CodePudding

Please can you tell me what is the table/view that stores informations about created types in Postgresql.

In Oracle the view is called "user_types" but i can't find the equivalent in Postgresql

Rq : the view pg_type isn't the one. that one stores information about data types. But i need information about created types.

Thank you !

CodePudding user response：

pg_type does store user created types:

CREATE TYPE compfoo AS (f1 int, f2 text);

select * from pg_type where typname = 'compfoo';
 oid   | typname 
-------- ---------
 128397 | compfoo

CodePudding user response：

pg_type will contain the types you created using CREATE TYPE

Oracle's user_types only contains the one the current user owns, so the equivalent in Postgres is

select *
from pg_type
where typowner = current_user::regrole;

To get the attributes defined in the type, you will need to use pg_class and join it to pg_attribute

The views from information_schema might be easier to use for this:

select udt.user_defined_type_schema,
       udt.user_defined_type_name, 
       att.attribute_name, 
       att.data_type, 
       att.is_nullable,
       att.character_maximum_length, 
       att.numeric_precision, 
       att.numeric_scale, 
       att.datetime_precision
from information_schema.user_defined_types udt
  join information_schema.attributes att 
    on att.udt_name = udt.user_defined_type_name 
   and att.udt_schema = udt.user_defined_type_schema
order by att.ordinal_position