How to Detach Partition in PostgreSQL: Syntax and Example

In PostgreSQL, you detach a partition from a partitioned table using the ALTER TABLE parent_table DETACH PARTITION partition_table; command. This removes the partition from the parent but keeps the partition table intact as a standalone table.

Syntax

The basic syntax to detach a partition in PostgreSQL is:

• ALTER TABLE parent_table DETACH PARTITION partition_table;

Here, parent_table is the main partitioned table, and partition_table is the child partition you want to detach. Detaching removes the partition from the parent but does not delete the partition table itself.

sql

ALTER TABLE parent_table DETACH PARTITION partition_table;

Example

This example shows how to detach a partition named sales_2023 from a partitioned table sales. After detaching, sales_2023 becomes a regular table.

sql

CREATE TABLE sales (
    id serial PRIMARY KEY,
    sale_date date NOT NULL,
    amount numeric
) PARTITION BY RANGE (sale_date);

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

-- Detach the partition
ALTER TABLE sales DETACH PARTITION sales_2023;

-- Check if sales_2023 is now a standalone table
\d sales_2023

Output

Table "public.sales_2023"
Column   |  Type   | Modifiers
---------+---------+----------
id       | integer | not null default nextval('sales_id_seq'::regclass)
sale_date| date    | not null
amount   | numeric | 
Indexes:
    "sales_2023_pkey" PRIMARY KEY, btree (id)

Common Pitfalls

Common mistakes when detaching partitions include:

• Trying to detach a partition that does not exist or is not attached to the parent table.
• Expecting the partition table to be deleted after detaching; it remains as a standalone table.
• Not handling data consistency or constraints after detaching, which may affect queries.

Always verify the partition exists and plan for how to manage the detached table.

sql

/* Wrong: Detaching a non-existent partition */
ALTER TABLE sales DETACH PARTITION sales_2022;

/* Correct: Detach an existing partition */
ALTER TABLE sales DETACH PARTITION sales_2023;

Quick Reference

Summary tips for detaching partitions in PostgreSQL:

• Use ALTER TABLE parent DETACH PARTITION child; to detach.
• The detached partition remains as a normal table.
• Check partition existence with pg_inherits or pg_partitioned_table system catalogs.
• Plan for data and constraint management after detaching.

Key Takeaways

Use ALTER TABLE DETACH PARTITION to remove a partition from its parent table.

Detaching a partition keeps the partition table intact as a standalone table.

Verify the partition exists before detaching to avoid errors.

Plan how to manage data and constraints after detaching a partition.

Detached partitions can be queried and managed like regular tables.