Attaching and detaching partitions in PostgreSQL - Step-by-Step Execution

Concept Flow - Attaching and detaching partitions

Start with parent table

↓

Create or have child partition

↓

Attach partition to parent

↓

Data in partition now part of parent

↓

Detach partition from parent

↓

Partition becomes standalone table

↓

End

You start with a main table and separate child tables (partitions). You attach a child to the parent to include its data, or detach it to make it independent.

Execution Sample

PostgreSQL

CREATE TABLE measurement (
  id INT,
  city TEXT,
  temp INT
) PARTITION BY LIST (city);

CREATE TABLE measurement_ny (
  id INT,
  city TEXT,
  temp INT
) PARTITION OF measurement FOR VALUES IN ('New York');

ALTER TABLE measurement ATTACH PARTITION measurement_ny FOR VALUES IN ('New York');

ALTER TABLE measurement DETACH PARTITION measurement_ny;

This code creates a partitioned table, a partition for New York, attaches it, then detaches it.

Execution Table

Step	Action	Table State	Result
1	Create parent table 'measurement' partitioned by city	Parent table 'measurement' exists, no partitions attached	Ready for partitions
2	Create child table 'measurement_ny' for city 'New York'	Child table 'measurement_ny' exists, not attached	Standalone table created
3	Attach 'measurement_ny' as partition of 'measurement'	Partition 'measurement_ny' attached to 'measurement'	Data in 'measurement_ny' now part of 'measurement' partitions
4	Detach 'measurement_ny' from 'measurement'	Partition 'measurement_ny' detached, standalone table again	Data no longer part of 'measurement' partitions

💡 All steps complete; partition attached then detached successfully

Variable Tracker

Table	Initial State	After Attach	After Detach
measurement	Partitioned parent table, no partitions	Partitioned parent with 'measurement_ny' attached	Partitioned parent, 'measurement_ny' detached
measurement_ny	Standalone table	Attached partition of 'measurement'	Standalone table again

Key Moments - 3 Insights

Why does the child table 'measurement_ny' exist before attaching?

The child table must exist first as a standalone table before attaching it as a partition, as shown in step 2 of the execution_table.

What happens to data in 'measurement_ny' after attaching?

After attaching (step 3), data in 'measurement_ny' becomes part of the parent table's partitions and is accessible through the parent.

Does detaching delete the child table?

No, detaching (step 4) only removes the partition link; the child table remains as a standalone table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the state of 'measurement_ny' after step 2?

A Standalone table, not attached

B Attached partition of 'measurement'

C Deleted table

D Parent table

💡 Hint

Check the 'Table State' column for step 2 in execution_table

At which step does 'measurement_ny' become part of the parent table's partitions?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look for 'attached' in the 'Table State' column in execution_table

If we skip the detach step, what is the final state of 'measurement_ny'?

A Standalone table

B Attached partition

C Deleted table

D Parent table

💡 Hint

Refer to variable_tracker column 'After Attach' for 'measurement_ny'

Concept Snapshot

Attaching and detaching partitions in PostgreSQL:
- Create child table first
- Use ALTER TABLE ... ATTACH PARTITION to link child
- Data in child is included in parent queries
- Use ALTER TABLE ... DETACH PARTITION to unlink
- Detached child remains a standalone table

Full Transcript

This visual execution shows how to attach and detach partitions in PostgreSQL. First, you create a parent table partitioned by a column. Then, create a child table for specific partition values. Attaching the child table links it to the parent, making its data part of the parent table's partitions. Detaching removes this link but keeps the child table intact. The execution table tracks each step and the state of tables. Key moments clarify common confusions like the child's existence before attaching and data inclusion after attaching. The quiz tests understanding of these states and transitions.

Practice

1. What does attaching a partition to a partitioned table in PostgreSQL do?

easy

A. It adds an existing table as a part of the partitioned table.

B. It deletes the data from the existing table.

C. It merges two tables into one without partitioning.

D. It creates a new partitioned table from scratch.

Solution

1. Step 1: Understand the purpose of attaching partitions

   Attaching a partition means linking an existing table to a partitioned table so it becomes one of its parts.

2. Step 2: Clarify what happens to data

   The data in the existing table remains intact and is now managed as part of the partitioned table.

3. Final Answer:

   It adds an existing table as a part of the partitioned table. -> Option A

4. Quick Check:

   Attaching partitions = adding existing table [OK]

Hint: Attaching means linking existing tables to partitions [OK]

Common Mistakes:

• Thinking attaching deletes data
• Confusing attaching with creating new tables
• Assuming attaching merges tables without partitioning

2. Which of the following is the correct syntax to attach a partition named part_2023 to a partitioned table sales in PostgreSQL?

easy

A. ALTER TABLE sales ADD PARTITION part_2023;

B. ALTER TABLE sales ATTACH PARTITION part_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

C. ALTER TABLE sales ATTACH part_2023 PARTITION;

D. ALTER TABLE sales ATTACH PARTITION part_2023;

Solution

1. Step 1: Recall the correct syntax for attaching partitions

   In PostgreSQL, when attaching an existing partitioned table, the syntax is ALTER TABLE sales ATTACH PARTITION part_2023; without specifying the value range, because the partition table already has the range defined.

2. Step 2: Check each option

   ALTER TABLE sales ATTACH PARTITION part_2023; uses the correct syntax. ALTER TABLE sales ATTACH PARTITION part_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'); incorrectly includes the value range, which is only used when creating a new partition, not attaching an existing one. Options C and D use invalid syntax.

3. Final Answer:

   ALTER TABLE sales ATTACH PARTITION part_2023; -> Option D

4. Quick Check:

   Attach existing partition without value range [OK]

Hint: Attach existing partitions without value ranges using ATTACH PARTITION [OK]

Common Mistakes:

• Including FOR VALUES clause when attaching existing partitions
• Using ADD PARTITION instead of ATTACH PARTITION
• Incorrect keyword order

3. Given the following commands, what will be the result of querying SELECT count(*) FROM sales; after detaching the partition part_2022?

ALTER TABLE sales DETACH PARTITION part_2022;
SELECT count(*) FROM sales;

medium

A. The count includes rows from all partitions including part_2022.

B. The count will be zero because detaching deletes data.

C. The count excludes rows from part_2022 because it was detached.

D. The query will fail with an error because part_2022 is detached.

Solution

1. Step 1: Understand what detaching a partition does

   Detaching removes the partition from the partitioned table but does not delete its data; it becomes a standalone table.

2. Step 2: Effect on queries on the partitioned table

   After detaching, queries on the partitioned table no longer include data from the detached partition.

3. Final Answer:

   The count excludes rows from part_2022 because it was detached. -> Option C

4. Quick Check:

   Detached partitions excluded from parent queries [OK]

Hint: Detached partitions are excluded from parent table queries [OK]

Common Mistakes:

• Assuming detaching deletes data
• Thinking detached partitions still count in queries
• Expecting query errors after detach

4. You run the command ALTER TABLE sales DETACH PARTITION part_2023; but get an error saying the partition does not exist. What is the most likely cause?

medium

A. The partition part_2023 is not attached to the sales table.

B. You need to use DROP PARTITION instead of DETACH PARTITION.

C. The sales table is not partitioned.

D. You must detach partitions before attaching them.

Solution

1. Step 1: Check if the partition exists and is attached

   The error indicates part_2023 is not currently attached to sales, so it cannot be detached.

2. Step 2: Understand the difference between detach and drop

   DETACH PARTITION removes the link but keeps data; DROP PARTITION deletes data. Using drop won't fix a non-attached partition error.

3. Final Answer:

   The partition part_2023 is not attached to the sales table. -> Option A

4. Quick Check:

   Detach fails if partition not attached [OK]

Hint: Detach only works on attached partitions [OK]

Common Mistakes:

• Trying to detach partitions not attached
• Confusing DETACH with DROP
• Ignoring if table is partitioned

5. 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?

hard

A. 1. ALTER TABLE archived_orders ATTACH PARTITION orders_2020;
2. ALTER TABLE orders DETACH PARTITION orders_2020;

B. 1. 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');

C. 1. DROP TABLE orders_2020;
2. CREATE TABLE archived_orders PARTITION OF orders FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

D. 1. ALTER TABLE orders ATTACH PARTITION orders_2020;
2. ALTER TABLE archived_orders DETACH PARTITION orders_2020;

Solution

1. Step 1: Detach the partition from the original table

   Use ALTER TABLE orders DETACH PARTITION orders_2020; to remove the partition without deleting data.

2. 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.

3. Final Answer:

   Detach from orders, then attach to archived_orders with value range. -> Option B

4. Quick Check:

   Detach then attach to new table preserves data [OK]

Hint: 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