Table Partitions should be in a folder inside their parent table

Right now for Postgres a partitioned tables partitions show up as tables in the table list. https://www.postgresql.org/docs/11/static/ddl-partitioning.html It would be more logical for the partitions to show up inside a sub folder of the partitioned table.

e.g For a partitioned table measurement with partitions measurement_y2006m02 and measurement_y2006m03 that the tree would be:

  • measurement
    • Column1
    • Column2
    • Indexes
      • Index1
    • Triggers
    • Partitions
      • measurement_y2006m02
      • measurement_y2006m03

With primary functions on the partitions of drop, attach, detach & create (though create might get a bit complicated so is less important)

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • I notice from Postgres_ListDatabaseTables in PostgreSQLBackingQueries.plist your the query is:

    SELECT
    
        table_schema,
    
        table_name
    
    FROM
    
        information_schema.tables
    
    WHERE
    
        table_type LIKE '%TABLE%'
    
        AND table_catalog != 'postgres '
    
        AND table_schema NOT IN (
    
            'information_schema', 'pg_catalog'
    
        )
    
        {SCHEMA_CLAUSE}
    
    UNION ...

    to filter out partitions from this the query would look similar to this:

    SELECT
        table_schema,
        table_name ,
        relkind, /* r (relation), v (view), i (index), t (toast), m (materialised), p (partitioned) */
        relispartition /* True or False */
    FROM
        information_schema.tables
        JOIN pg_namespace ON pg_namespace.nspname = table_schema
        JOIN pg_class     ON pg_class.relnamespace = pg_namespace.oid AND pg_class.relname = table_name
    WHERE
        table_type LIKE '%TABLE%'
        AND table_catalog != 'postgres '
        AND table_schema NOT IN (
            'information_schema', 'pg_catalog'
        )
         {SCHEMA_CLAUSE}
    UNION ...

    And you can filter on AND relispartition = False to remove the partitions and use relkind to indicate it is a partitioned table 

    Like
    • Paul Schaap interesting.. how would a 'table partition' be treated in the database tree? Like a table? IE, does it have columns if you expand it? If you double click it would it do a 'select * from <table-partition>'?

      Like
    • Kyle Hankinson I would think a new folder called Partitions would be in the table 'folder' just like Indexes and Triggers are right now. In Postgres (and many other databases) partitions are technically tables so have columns, can be selected directly and dropped etc. so would have similar right click activities to a regular table. Happy Easter !

      Like
Like Follow
  • 9 mths agoLast active
  • 3Replies
  • 77Views
  • 2 Following