List PostgreSQL Partitioned Tables with SQL

calendar Apr 9, 2026 / · 4 min read · postgresql administration partitioning database pg_class pg_namespace information_schema pg_inherits  ·
Share on: linkedin copy

How to List PostgreSQL Partitioned Tables

PostgreSQL table partitioning splits a large table into smaller physical pieces called child partitions. This improves query performance and simplifies data lifecycle management. But as a DBA, you need a quick way to see which tables in your database use partitioning — and which do not.

This SQL query reads directly from the PostgreSQL system catalogs to list every table in your database, labeling each as a partitioned table or an ordinary table, while excluding system schemas and child partition tables.

SQL Script

 1SELECT
 2    n.nspname AS "Schema",
 3    c.relname AS "Name",
 4    CASE c.relkind
 5    WHEN 'p' THEN
 6        'partitioned table'
 7    WHEN 'r' THEN
 8        'ordinary table'
 9    ELSE
10        'unknown table type'
11    END AS "Type",
12    pg_catalog.pg_get_userbyid(c.relowner) AS "Owner"
13FROM
14    pg_catalog.pg_class c
15    JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
16WHERE
17    c.relkind = ANY ('{p,r,""}')
18    AND NOT c.relispartition
19    AND n.nspname !~ ALL ('{^pg_,^information_schema$}')
20ORDER BY
21    1,
22    2;

Notes: Works on PostgreSQL 10 and later, when declarative partitioning was introduced. Returns one row per table. Child partitions are excluded — only parent partitioned tables appear in the results.

Code Breakdown

pg_catalog.pg_class

The system catalog table that holds one row for every relation (table, index, sequence, view, etc.) in the database.

pg_catalog.pg_namespace

Holds schema names. Joined to pg_class on c.relnamespace = n.oid to get the human-readable schema name.

c.relkind

A single character that identifies the relation type. p means partitioned table, r means ordinary (regular) table.

CASE c.relkind

Translates the single-character code into a readable label. Only p and r are handled here; anything else returns unknown table type.

pg_catalog.pg_get_userbyid(c.relowner)

Converts the owner's OID (a numeric ID) into the role name that owns the table.

c.relkind = ANY ('{p,r,""}')

Filters to only partitioned tables and ordinary tables, skipping indexes, sequences, views, and other relation types.

NOT c.relispartition

Excludes child partition tables. Without this filter, every partition of every partitioned table would appear in the results. Only the parent table is shown.

n.nspname !~ ALL ('{^pg_,^information_schema$}')

Excludes system schemas. pg_ covers all internal PostgreSQL schemas. information_schema is the SQL-standard metadata schema.

ORDER BY 1, 2

Sorts results by schema name first, then table name, for easy reading.

Key Points

  • Partitioned tables show as partitioned table; all other user tables show as ordinary table.
  • Child partitions (the actual physical storage pieces) are hidden by the NOT c.relispartition filter.
  • The query covers all user schemas. To restrict to one schema, add AND n.nspname = 'your_schema'.
  • The Owner column shows which role owns each table, which is useful when auditing permissions.

Insights and Explanations

PostgreSQL declarative partitioning (introduced in version 10) lets you split a table by a partition key — commonly a date range, a list of values, or a hash. Each child partition holds a subset of the rows. PostgreSQL routes queries to only the relevant partitions, which can dramatically cut query time on large tables.

This query helps you answer two questions quickly:

  1. Which tables in my database are partitioned?
  2. Which tables are plain ordinary tables that might benefit from partitioning if they grow large?

If a table appears as partitioned table, it is a parent table with child partitions beneath it. You can query pg_inherits to see those children:

1SELECT
2    parent.relname AS parent_table,
3    child.relname  AS partition_name
4FROM
5    pg_inherits
6    JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
7    JOIN pg_class child  ON pg_inherits.inhrelid  = child.oid
8WHERE
9    parent.relname = 'your_table_name';

If you see a table you expected to be partitioned showing as ordinary table, it may not have had partitioning applied yet, or the migration is still pending.

Additional Considerations

  • PostgreSQL version: Declarative partitioning exists from PostgreSQL 10. On PostgreSQL 9.x and earlier, partitioning was done via table inheritance and trigger-based routing — this query will not identify those older-style partitioned tables correctly.
  • Permissions: Any user can read pg_class and pg_namespace. No superuser access is required.
  • Performance: This query reads only system catalog tables, which are small. It is safe to run at any time with no impact on production workloads.
  • Child partition count: This query does not show how many partitions each parent has. Use pg_inherits for that, or join to it here if needed.

References

Posts in this series