Attaching and detaching partitions in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of attaching a partition

Given a partitioned table sales and a child table sales_2023 with matching structure, what is the output of the following command?

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

What will be the result of this command?

PostgreSQL

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

A Syntax error due to missing parentheses around the range values.

B Error because the range values overlap with an existing partition.

C Error because sales_2023 already contains data.

D The partition sales_2023 is attached successfully to sales.

💡 Hint

Check if the partition range matches the parent table's partitioning scheme.

The command attaches the child table sales_2023 as a partition for the specified range. If the range matches and no conflicts exist, it succeeds.

❓ query_result

intermediate

Result of detaching a partition

What happens when you run this command on a partitioned table orders with a partition orders_2022 attached?

ALTER TABLE orders DETACH PARTITION orders_2022;

PostgreSQL

ALTER TABLE orders DETACH PARTITION orders_2022;

A Error because the partition contains data.

B Syntax error due to missing CASCADE keyword.

C The partition orders_2022 is detached and becomes a standalone table.

D The partition is detached but all data is deleted.

💡 Hint

Detaching a partition removes it from the parent but keeps the data intact.

Detaching a partition removes the child table from the partitioned table but does not delete its data. It becomes a normal table.

📝 Syntax

advanced

Identify the syntax error in attaching a partition

Which option contains a syntax error when trying to attach a partition log_2023 to a partitioned table logs for the year 2023?

A ALTER TABLE logs ATTACH PARTITION log_2023 FOR VALUES IN ('2023');

B ALTER TABLE logs ATTACH PARTITION log_2023 FOR VALUES FROM '2023-01-01' TO '2024-01-01';

C ALTER TABLE logs ATTACH PARTITION log_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

D ;)'10-10-4202'( OT )'10-10-3202'( MORF SEULAV ROF 3202_gol NOITITRAP HCATTA sgol ELBAT RETLA

💡 Hint

Check the syntax for specifying range values in the ATTACH PARTITION command.

The FROM and TO values must be enclosed in parentheses. Option B misses the parentheses causing a syntax error.

🔧 Debug

advanced

Why does attaching a partition fail?

You try to attach a partition events_2023 to a partitioned table events using:

ALTER TABLE events ATTACH PARTITION events_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

The command fails with an error about overlapping ranges. What is the most likely cause?

A There is already a partition attached with a range that overlaps the specified range.

B The child table events_2023 has a different column structure than events.

C The parent table events is not partitioned.

D The child table events_2023 contains no data.

💡 Hint

Check existing partitions and their ranges for conflicts.

Partition ranges cannot overlap. If an existing partition covers any part of the specified range, attaching fails.

🧠 Conceptual

expert

Effect of detaching a partition on indexes

After detaching a partition archive_2020 from a partitioned table archive, what happens to the indexes defined on the partitioned table?

A The detached partition retains its own indexes independently of the parent table.

B All indexes on the detached partition are dropped automatically.

C The detached partition inherits all indexes from the parent table dynamically.

D The detached partition loses all indexes and must be reindexed manually.

💡 Hint

Consider how indexes are managed on partitions versus standalone tables.

Partitions have their own indexes. Detaching makes the partition a standalone table, so its indexes remain intact.