Show only list of tables without child partitions

Query to get all ordinary tables, including root partitioned tables, but excluding all non-root partitioned tables:

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() is instrumental to get the name of the owning role.

There are more types of "tables" in 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 also added the meta-command \dP to 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.

So \dPt gets all root partitioned tables - but not ordinary tables.

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.

If you don't have to use a \d* shortcut, this query should work (though you'll need to filter out schemas not in your search_path):

SELECT relname FROM pg_class WHERE relkind IN ('r','p') AND NOT relispartition;

--do some test in greenplum 6.14 --to find normal table ,partition table

SELECT
    * 
FROM
    (
    SELECT
        t1.schemaname,
        t1.tablename,
        t1.tableowner,
    CASE
            
            WHEN t3.tablename IS NULL 
            AND t2.tablename IS NULL THEN
                'r' 
                WHEN t3.tablename IS NOT NULL 
                AND t2.tablename IS NULL THEN
                    'p-root' ELSE'p-child' 
                    END AS tabletype,
                t4.actionname,
                t4.statime 
            FROM
                pg_tables t1
                LEFT JOIN pg_partitions t2 ON t1.tablename = t2.partitiontablename 
                AND t1.schemaname = t2.partitionschemaname
                LEFT JOIN ( SELECT DISTINCT schemaname, tablename FROM pg_partitions WHERE schemaname IN ( 'public', 'ods', 'tmp' ) ) t3 ON t1.tablename = t3.tablename 
                AND t1.schemaname = t3.schemaname
                LEFT JOIN (
                SELECT
                    * 
                FROM
                    (
                    SELECT
                        schemaname,
                        objname,
                        actionname,
                        statime,
                        ROW_NUMBER ( ) OVER ( PARTITION BY schemaname, objname ORDER BY statime DESC ) AS rn 
                    FROM
                        pg_catalog.pg_stat_operations 
                    WHERE
                        classname = 'pg_class' 
                        AND schemaname IN ( 'public', 'ods', 'tmp' ) 
                        AND actionname IN ( 'CREATE', 'ALTER' ) 
                    ) n 
                WHERE
                    n.rn = 1 
                ) t4 ON t1.schemaname = t4.schemaname 
                AND t1.tablename = t4.objname 
            WHERE
                t1.schemaname IN ( 'public', 'ods', 'tmp' ) 
            ) o 
        WHERE
            tabletype IN ( 'r', 'p-root' ) 
        ORDER BY
            1,
            2,
        3,
    4