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.