Show only list of tables without child partitions

Question

I would like to show only the list of top level tables without child partitioned tables in PostgreSQL. (Currently using PostgreSQL 12.)

\dt in psql gives me all tables, including partitions of tables. I see this:

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

But I want a list like this, without child partitions of the parent partitioned table:

                         List of relations

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

Answer 1

Version 1

I can't test this right now, but this ought to give you only top-level tables that are partitioned

select relname
  from pg_class
 where oid in (select partrelid from pg_partitioned_table);

You should be able to refine/expand that to get more details.

Version 2

Here's a comically verbose solution:

with 
partition_parents as (
    select relnamespace::regnamespace::text as schema_name,
           relname as table_name,
           'partition_parent' as info,
           *
      from pg_class
     where relkind = 'p'), -- The parent table is relkind 'p', the partitions are regular tables, relkind 'r'

unpartitioned_tables as (     
    select relnamespace::regnamespace::text as schema_name,
           relname as table_name,
           'unpartitioned_table' as info,
           *
      from pg_class
     where relkind = 'r'
           and not relispartition
     ) -- Regular table

select * from partition_parents where schema_name not in ('information_schema','pg_catalog','api','extensions') -- Whatever you've got for schemas
union
select * from unpartitioned_tables where schema_name not in ('information_schema','pg_catalog','api','extensions') -- Whatever you've got for schemas
order by 1,2,3 

You should be able to cut the size of that way down to match what you really want. Someone here who really gets the system catalogs should likely be able to provide a more concise version. In the plus column, it's kind of nice to add in the "partition_parent" versus "unpartitioned_table" detail, as above.