When querying pg_type, I'm interested in identifying free-standing composite types (user-defined) and not those which are created along with tables. Perhaps there is a way to lookup pg_class (with typrelid) which might help but it's not apparent how I might do that.


Composite types that belong to tables have an internal dependency on the table in pg_depend. You simply have to exclude such types:

SELECT t.oid::regtype
FROM pg_type AS t
WHERE t.typtype = 'c'
   AND t.typnamespace NOT IN
          ('pg_catalog'::regnamespace, 'pg_toast'::regnamespace)
          SELECT 1 FROM pg_depend AS d
          WHERE d.objid = t.oid
            AND d.classid = 'pg_type'::regclass
            AND d.refclassid = 'pg_class'::regclass
            AND d.deptype = 'i');
| improve this answer | |

i believe i've found the answer - pg_class (which can be joined to pg_type ON pg_type.typrelid = pg_class.oid) has a column called relkind which provides:

r = ordinary table
i = index
S = sequence
v = view
c = composite type *** identifies free-standing composite type
t = TOAST table

This query identifies custom (free-standing) types:

select t.typname, t.typtype, t.typcategory, t.typrelid, c.relname, c.relkind
from pg_type t
left join pg_class c on c.oid = t.typrelid
where t.typtype = 'c' and c.relkind = 'c';
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.