View Detailed Information on Sales Table Partitions

Checking Partitioned Table Information

Write a PostgreSQL query to display partition details for the Sales table.

Solution:

-- Get details of partitions in the Sales table.
-- Select partition name and parent table using type conversion to regclass for readability.
SELECT inhrelid::regclass AS partition_name, inhparent::regclass AS parent_table
-- Retrieve the information from the system catalog table that stores inheritance details.
FROM pg_inherits
-- Filter to include only partitions where the parent table is Sales.
WHERE inhparent = 'Sales'::regclass;

Explanation:

• Purpose of the Query:
• Retrieves information about existing partitions.
• Key Components:
• Queries pg_inherits, the system catalog table for partitions.
• Real-World Application:
• Useful for verifying partitioning structure in large databases.

Notes:

• Run this query before performing partition management tasks.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to list all partitions of a given table along with their row counts.
• Write a PostgreSQL query to display each partition’s boundaries and partitioning key values.
• Write a PostgreSQL query to retrieve the storage size for each partition in a partitioned table.
• Write a PostgreSQL query to check the last modification timestamp for each partition of a table.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Creating an Index on a Partitioned Table.