w3resource

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.

Next PostgreSQL Exercise: Inserting Data into a Partitioned Table.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.