Query to find list of Column Oriented table(append_only tables) and partitioned tables

Question

Need help

1. How can I list the tables which are column oriented in any database?

2. How can I list the tables which are created with partition in any database?

Thanks

Answer 1

In Greenplum you cannot issue cross-database queries, and as the catalog is placed inside of each database, you cannot list the tables in "all" the databases at the same time. But for each of the databases you can easily make it with this queries:

-- List all the column-oriented tables in current database
select  n.nspname as schemaname,
        c.relname as tablename
    from pg_class as c, pg_namespace as n
    where c.relnamespace = n.oid
        and c.relstorage = 'c';

-- List all partitioned tables in current database
select schemaname,
       tablename,
       count(*) as num_partitions
    from pg_partitions
    group by 1, 2;

Answer 2

It will also help

select relname from pg_class where reloptions= '{appendonly=true,orientation=column}';

select * from pg_partitions;

Thanks