Schema Exploration

select
  c.reltuples::bigint as estimated_rows
, c.relpages as estimated_pages
, pg_catalog.pg_total_relation_size(c.oid) as total_bytes
, c.relkind = 'p' as is_partitioned_parent
, c.relispartition as is_partition
, exists (
    select 1
    from pg_catalog.pg_inherits i
    join pg_catalog.pg_class child on child.oid = i.inhrelid
    where i.inhparent = c.oid
      and not child.relispartition
  ) as has_inheritance_children
, not c.relispartition and exists (
    select 1
    from pg_catalog.pg_inherits i
    where i.inhrelid = c.oid
  ) as has_inheritance_parent
, exists (
    select 1
    from pg_catalog.pg_trigger t
    where t.tgrelid = c.oid
      and not t.tgisinternal
  ) as has_user_triggers
from pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
where n.nspname = $1
  and c.relname = $2
  and c.relkind in ('r', 'p', 'f')
;

select
  a.attnum as position
, a.attname as column_name
, pg_catalog.format_type(a.atttypid, a.atttypmod) as data_type
, a.attnotnull as not_null
, pg_catalog.pg_get_expr(d.adbin, d.adrelid) as default_or_generation_expression
, a.attidentity as identity_kind
, a.attgenerated as generated_kind
, pg_catalog.col_description(a.attrelid, a.attnum) as comment
from pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
join pg_catalog.pg_attribute a on a.attrelid = c.oid
left join pg_catalog.pg_attrdef d on d.adrelid = c.oid and d.adnum = a.attnum
where n.nspname = $1
  and c.relname = $2
  and c.relkind in ('r', 'p', 'f')
  and a.attnum > 0
  and not a.attisdropped
order by a.attnum
;

select
  con.conname as constraint_name
, con.contype as kind
, con.convalidated as validated
, pg_catalog.pg_get_constraintdef(con.oid, true) as definition
, pg_catalog.obj_description(con.oid, 'pg_constraint') as comment
from pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
join pg_catalog.pg_constraint con on con.conrelid = c.oid
where n.nspname = $1
  and c.relname = $2
  and c.relkind in ('r', 'p', 'f')
order by con.contype, con.conname
;

select
  sn.nspname as from_schema
, src.relname as from_table
, con.conname as constraint_name
, pg_catalog.pg_get_constraintdef(con.oid, true) as definition
, tn.nspname as to_schema
, dst.relname as to_table
, con.convalidated as validated
from pg_catalog.pg_constraint con
join pg_catalog.pg_class src on src.oid = con.conrelid
join pg_catalog.pg_namespace sn on sn.oid = src.relnamespace
join pg_catalog.pg_class dst on dst.oid = con.confrelid
join pg_catalog.pg_namespace tn on tn.oid = dst.relnamespace
where con.contype = 'f'
  and ((sn.nspname = $1 and src.relname = $2)
    or (tn.nspname = $1 and dst.relname = $2))
order by sn.nspname, src.relname, con.conname
;

select distinct
  vn.nspname as view_schema
, v.relname as view_name
, case v.relkind when 'm' then 'materialized view' else 'view' end as view_kind
from pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
join pg_catalog.pg_depend d on d.refclassid = 'pg_catalog.pg_class'::pg_catalog.regclass
  and d.refobjid = c.oid
  and d.classid = 'pg_catalog.pg_rewrite'::pg_catalog.regclass
join pg_catalog.pg_rewrite r on r.oid = d.objid and r.rulename = '_RETURN'
join pg_catalog.pg_class v on v.oid = r.ev_class and v.relkind in ('v', 'm')
join pg_catalog.pg_namespace vn on vn.oid = v.relnamespace
where n.nspname = $1
  and c.relname = $2
  and c.relkind in ('r', 'p', 'f')
  and v.oid <> c.oid
order by vn.nspname, v.relname
;

select
  i.relname as index_name
, pg_catalog.pg_get_indexdef(i.oid) as definition
, ix.indisunique as unique_index
, ix.indisvalid as valid
, ix.indisready as ready
, pg_catalog.pg_relation_size(i.oid) as bytes
from pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
join pg_catalog.pg_index ix on ix.indrelid = c.oid
join pg_catalog.pg_class i on i.oid = ix.indexrelid
where n.nspname = $1
  and c.relname = $2
order by i.relname
;