snippetsqlMinor
How to distinguish free-standing composite types from table composite types in pg_type?
Viewed 0 times
typesfreestandingpg_typedistinguishcompositehowfromtable
Problem
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.Solution
i believe i've found the answer -
This query identifies custom (free-standing) types:
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 tableThis 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';Code Snippets
r = ordinary table
i = index
S = sequence
v = view
c = composite type *** identifies free-standing composite type
t = TOAST tableselect 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';Context
StackExchange Database Administrators Q#238022, answer score: 4
Revisions (0)
No revisions yet.