w3resource

Separate Orders_East Partition as a Standalone Table


Detaching a Partition from a Partitioned Table

Write a PostgreSQL query to detach Orders_East partition from the Orders table.

Solution:

-- Detach the partition named Orders_East from the Orders table without deleting the partition table
ALTER TABLE Orders DETACH PARTITION Orders_East;

Explanation:

  • Purpose of the Query:
    • Converts a partition back into a standalone table.
  • Key Components:
    • DETACH PARTITION: Removes a partition from the parent without deleting data.
  • Real-World Application:
    • Useful for migrating partitions to separate storage before deletion.

Notes:

  • The detached partition still contains its data.

For more Practice: Solve these Related Problems:

  • Write a PostgreSQL query to detach a partition and simultaneously rename it to indicate its detached status.
  • Write a PostgreSQL query to detach a partition and convert it into a temporary table for data review.
  • Write a PostgreSQL query to detach a partition only if the partition meets certain data volume criteria.
  • Write a PostgreSQL query to detach a partition and then reattach it under a different partitioning strategy.


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

Previous PostgreSQL Exercise: Attaching an Existing Table as a Partition.

Next PostgreSQL Exercise: Renaming a Partition in 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.