Which sequence of commands correctly achieves this without data loss?
hard📝 Application Q15 of 15
PostgreSQL - Table Partitioning
You have a large partitioned table orders partitioned by range on order_date. You want to archive data from 2020 by detaching the partition orders_2020 and then attaching it to a new partitioned table archived_orders. Which sequence of commands correctly achieves this without data loss?
A1. ALTER TABLE archived_orders ATTACH PARTITION orders_2020;
2. ALTER TABLE orders DETACH PARTITION orders_2020;
B1. ALTER TABLE orders DETACH PARTITION orders_2020;
2. ALTER TABLE archived_orders ATTACH PARTITION orders_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
C1. DROP TABLE orders_2020;
2. CREATE TABLE archived_orders PARTITION OF orders FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
D1. ALTER TABLE orders ATTACH PARTITION orders_2020;
2. ALTER TABLE archived_orders DETACH PARTITION orders_2020;
Step-by-Step Solution
Solution:
Step 1: Detach the partition from the original table
Use ALTER TABLE orders DETACH PARTITION orders_2020; to remove the partition without deleting data.
Step 2: Attach the detached partition to the new partitioned table
Use ALTER TABLE archived_orders ATTACH PARTITION orders_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'); to add it as a partition.
Final Answer:
Detach from orders, then attach to archived_orders with value range. -> Option B
Quick Check:
Detach then attach to new table preserves data [OK]
Quick Trick:Detach first, then attach to new partitioned table [OK]
Common Mistakes:
Dropping the partition instead of detaching
Attaching before detaching
Not specifying value ranges when attaching
Master "Table Partitioning" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
