postgresql - 仅显示没有子分区的表列表

Question

我只想显示 PostgreSQL 中没有子分区表的顶级表列表。 (目前使用 PostgreSQL 12。)

\dt 在 psql 中给我所有的表，包括表的分区。我看到了这个:

postgres=# \dt

                         List of relations

 Schema |             Name             |       Type        | Owner  
--------+------------------------------+-------------------+--------  
public  | tablea                       | table             | me
public  | partitionedtable1            | partitioned table | me
public  | partitionedtable1_part1      | table             | me
public  | partitionedtable1_part2      | table             | me
public  | partitionedtable1_part3      | table             | me
public  | tableb                       | table             | me

但我想要一个这样的列表，没有父分区表的子分区:

                         List of relations

 Schema |             Name             |       Type        | Owner  
--------+------------------------------+-------------------+--------  
public  | tablea                       | table             | me
public  | partitionedtable1            | partitioned table | me
public  | tableb                       | table             | me

Answer 1

查询得到所有普通表，包括根分区表，但排除所有非根分区表:

SELECT n.nspname AS "Schema"
     , c.relname AS "Name"
     , CASE c.relkind
         WHEN 'p' THEN 'partitioned table'
         WHEN 'r' THEN 'ordinary table'
         -- more types?
         ELSE 'unknown table type'
       END AS "Type"
     , pg_catalog.pg_get_userbyid(c.relowner) AS "Owner"
FROM   pg_catalog.pg_class c
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind = ANY ('{p,r,""}')  -- add more types?
AND    NOT c.relispartition          -- exclude child partitions
AND    n.nspname !~ ALL ('{^pg_,^information_schema$}') -- exclude system schemas
ORDER  BY 1, 2;

The manual about relispartition:

... True if table or index is a partition

pg_get_userbyid()有助于获取拥有角色的名称。

Postgres 12 中有更多类型的“表”。The manual about relkind:

r = ordinary table, i = index, S = sequence, t = TOAST table,v = view, m = materialized view, c = composite type, f =foreign table, p = partitioned table, I = partitioned index

---

Postgres 12 还添加了元命令 \dP 到 psql:

The manual:

\dP[itn+] [ pattern ]

Lists partitioned relations. If pattern is specified, only entries whose name matches the pattern are listed. The modifiers t (tables) and i (indexes) can be appended to the command, filtering the kind of relations to list. By default, partitioned tables and indexes are listed.

If the modifier n (“nested”) is used, or a pattern is specified, then non-root partitioned relations are included, and a column is shown displaying the parent of each partitioned relation.

因此 \dPt 获取所有根分区表 - 但不是普通表。