HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to distinguish free-standing composite types from table composite types in pg_type?

Submitted by: @import:stackexchange-dba··
0
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 - 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';

Code Snippets

r = ordinary table
i = index
S = sequence
v = view
c = composite type *** identifies free-standing composite type
t = TOAST table
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';

Context

StackExchange Database Administrators Q#238022, answer score: 4

Revisions (0)

No revisions yet.