timescale/pg-aiguide / skills/schema-exploration
schema-exploration
VerifiedExplore an existing PostgreSQL database before answering questions about its data or writing SQL. Use this skill whenever a user asks for a query or a data-backed answer against an unfamiliar schema (counts, missing or failed records, recent changes), asks where a business concept lives, or asks how tables, joins, views, routines, triggers, RLS, or extensions work. Find the relevant objects with read-only pg_catalog queries, then request approval before inspecting data-derived statistics or rows. Not a schema-design or migration guide.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 4.3 KB |
| references/data-values.md | markdown | 5.7 KB |
| references/extensions.md | markdown | 1.8 KB |
| references/foreign-tables.md | markdown | 3.9 KB |
| references/overview.md | markdown | 5.9 KB |
| references/partitioning-and-inheritance.md | markdown | 6.7 KB |
| references/psql.md | markdown | 2.1 KB |
| references/query-authoring.md | markdown | 2.1 KB |
| references/routines.md | markdown | 2.9 KB |
| references/security.md | markdown | 2.5 KB |
| references/tables.md | markdown | 7.7 KB |
| references/triggers.md | markdown | 1.6 KB |
| references/views.md | markdown | 2.4 KB |
references/partitioning-and-inheritance.md
View on GitHub# Partitioning and inheritance: traverse a table's tree
Use the four structural flags in [tables](tables.md#size-and-object-signals) to choose a traversal. Each query uses `$1` = schema name, `$2` = relation name and returns **the selected relation at depth 0** plus its ancestors or descendants. A query for the wrong kind of relation returns no rows. Names are always schema-qualified in the result. Depth is the number of parent–child edges from the selected relation; a subpartition can be both a partition and a partitioned parent. Queries target PostgreSQL 18 and also run on PostgreSQL 14–17.
## Contents
- [Partition ancestors (up)](#partition-ancestors-up)
- [Partition descendants (down)](#partition-descendants-down)
- [Traditional inheritance ancestors (up)](#traditional-inheritance-ancestors-up)
- [Traditional inheritance descendants (down)](#traditional-inheritance-descendants-down)
## Partition ancestors (up)
Run when `is_partition` is true. Each ancestor row identifies the child through which it was reached. The selected partition's bounds and each ancestor's partition key are shown. `pg_inherits` edges are followed only when the child is a declarative partition.
```sql
with recursive walk as (
select
c.oid as relid
, null::oid as via_child_oid
, 0 as depth
, array[c.oid] as path
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.relispartition
union all
select
parent.oid
, child.oid
, w.depth + 1
, w.path || parent.oid
from walk w
join pg_catalog.pg_class child on child.oid = w.relid and child.relispartition
join pg_catalog.pg_inherits i on i.inhrelid = child.oid
join pg_catalog.pg_class parent on parent.oid = i.inhparent
where not parent.oid = any(w.path)
)
select
w.depth
, n.nspname as schema_name
, c.relname as relation_name
, cn.nspname as via_child_schema
, child.relname as via_child_name
, pg_catalog.pg_get_partkeydef(c.oid) as partition_key
, pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_bounds
from walk w
join pg_catalog.pg_class c on c.oid = w.relid
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
left join pg_catalog.pg_class child on child.oid = w.via_child_oid
left join pg_catalog.pg_namespace cn on cn.oid = child.relnamespace
order by w.depth
;
```
## Partition descendants (down)
Run when `is_partitioned_parent` is true. PostgreSQL's `pg_partition_tree()` already walks the declarative partition tree, so no custom recursive CTE is needed here. Each result row identifies its **direct parent**, whether it is a leaf, and its partition bounds. The root has no parent and depth (`level`) 0. This function has been available since PostgreSQL 12.
```sql
with root as materialized (
select c.oid
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 = 'p'
)
select
t.level as depth
, n.nspname as schema_name
, c.relname as relation_name
, pn.nspname as parent_schema
, parent.relname as parent_name
, t.isleaf as is_leaf
, pg_catalog.pg_get_partkeydef(c.oid) as partition_key
, pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_bounds
from root
cross join lateral pg_catalog.pg_partition_tree(root.oid) t
join pg_catalog.pg_class c on c.oid = t.relid
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
left join pg_catalog.pg_class parent on parent.oid = t.parentrelid
left join pg_catalog.pg_namespace pn on pn.oid = parent.relnamespace
order by t.level, n.nspname, c.relname
;
```
## Traditional inheritance ancestors (up)
Run when `has_inheritance_parent` is true. Follow only edges whose child is **not** a declarative partition. Each ancestor row identifies the child through which it was reached. Multiple inheritance can produce more than one path to a relation; paths are not collapsed.
```sql
with recursive walk as (
select
c.oid as relid
, null::oid as via_child_oid
, 0 as depth
, array[c.oid] as path
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 not c.relispartition
and exists (select 1 from pg_catalog.pg_inherits i where i.inhrelid = c.oid)
union all
select
parent.oid
, child.oid
, w.depth + 1
, w.path || parent.oid
from walk w
join pg_catalog.pg_class child on child.oid = w.relid and not child.relispartition
join pg_catalog.pg_inherits i on i.inhrelid = child.oid
join pg_catalog.pg_class parent on parent.oid = i.inhparent
where not parent.oid = any(w.path)
)
select
w.depth
, n.nspname as schema_name
, c.relname as relation_name
, cn.nspname as via_child_schema
, child.relname as via_child_name
from walk w
join pg_catalog.pg_class c on c.oid = w.relid
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
left join pg_catalog.pg_class child on child.oid = w.via_child_oid
left join pg_catalog.pg_namespace cn on cn.oid = child.relnamespace
order by w.depth, n.nspname, c.relname
;
```
## Traditional inheritance descendants (down)
Run when `has_inheritance_children` is true. Each descendant row names its direct parent; the starting relation has no parent in this traversal. Multiple inheritance can yield more than one path to a descendant.
```sql
with recursive walk as (
select
c.oid as relid
, null::oid as parent_oid
, 0 as depth
, array[c.oid] as path
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 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
)
union all
select
child.oid
, w.relid
, w.depth + 1
, w.path || child.oid
from walk w
join pg_catalog.pg_inherits i on i.inhparent = w.relid
join pg_catalog.pg_class child on child.oid = i.inhrelid and not child.relispartition
where not child.oid = any(w.path)
)
select
w.depth
, n.nspname as schema_name
, c.relname as relation_name
, pn.nspname as parent_schema
, parent.relname as parent_name
from walk w
join pg_catalog.pg_class c on c.oid = w.relid
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
left join pg_catalog.pg_class parent on parent.oid = w.parent_oid
left join pg_catalog.pg_namespace pn on pn.oid = parent.relnamespace
order by w.depth, n.nspname, c.relname
;
```
These trees describe catalog structure, not whether a query scans every child or where every row is stored. Ordinary inheritance and declarative partitioning have different query and constraint semantics. TimescaleDB hypertables require extension-specific interpretation; see [extensions](extensions.md).
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |