Re: Listing only the user defined types (with owners)

Hi

You can find all user defined types with the following query.

CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');

SELECT typname
FROM pg_catalog.pg_type
JOIN pg_catalog.pg_namespace
ON pg_namespace.oid = pg_type.typnamespace
WHERE
typtype = 'e' and nspname NOT IN ('pg_catalog', 'information_schema');
typname
-----------------------
bug_status

The values for typtype are as follows
typtype is b for a base type, c for a composite type (e.g., a table's row
type), d for a domain, e for an enum type, p for a pseudo-type, or r for a
range type. See also typrelid and typbasetype.

Regards
Kashif Zeeshan
Bitnine Global

On Thu, May 2, 2024 at 4:40 PM Durumdara <durumdara(at)gmail(dot)com> wrote:

> Hello!
>
> I have a script which can change the table owners to the database owner.
>
> I select the tables like this:
>
> FOR r IN SELECT tablename FROM pg_tables WHERE (schemaname = 'public')
> and (tableowner <> act_dbowner)
> LOOP
> ...
>
> For types I found pg_type, but this contains all types.
>
> For example I have only one user defined type, like "T_TEST", but this
> pg_type relation contains the basic data types, other data types, from any
> schema.
>
> Do you have a working Query which lists the user defined types with the
> owners?
>
> Thank you for your help!
>
> Best regards
> dd
>
>
>