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
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@xxxxxxxxx> 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 regardsdd
