How to Attach Partition in PostgreSQL: Syntax and Example

In PostgreSQL, you attach a partition to a partitioned table using the ALTER TABLE ... ATTACH PARTITION command. This command links an existing table as a partition of the parent table based on the defined partition key.

Syntax

The basic syntax to attach a partition in PostgreSQL is:

• ALTER TABLE parent_table ATTACH PARTITION child_table FOR VALUES ...;

Here, parent_table is the main partitioned table, child_table is the existing table to attach as a partition, and FOR VALUES specifies the partition key values that this partition will hold.

sql

ALTER TABLE parent_table ATTACH PARTITION child_table FOR VALUES IN (value1, value2, ...);

Example

This example shows how to create a partitioned table and attach an existing table as a partition for specific values.

sql

CREATE TABLE sales (
  id serial PRIMARY KEY,
  region text NOT NULL,
  amount numeric NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE sales_north PARTITION OF sales FOR VALUES IN ('north');

CREATE TABLE sales_south (
  id serial PRIMARY KEY,
  region text NOT NULL,
  amount numeric NOT NULL
);

-- Attach existing sales_south table as partition for 'south'
ALTER TABLE sales ATTACH PARTITION sales_south FOR VALUES IN ('south');

Common Pitfalls

Common mistakes when attaching partitions include:

• Trying to attach a table that does not match the partition key columns and data types.
• Not specifying the correct FOR VALUES clause matching the partitioned table's strategy.
• Attaching a table that already contains rows outside the specified partition values, causing errors.

Always ensure the child table's structure and data align with the partition key and values.

sql

/* Wrong: attaching a table with mismatched partition key */
ALTER TABLE sales ATTACH PARTITION sales_east FOR VALUES IN ('east');

/* Right: ensure sales_east matches partition key and values */

Quick Reference

Command	Description
ALTER TABLE parent_table ATTACH PARTITION child_table FOR VALUES ...	Attach an existing table as a partition
FOR VALUES IN (...)	Specify list partition values
FOR VALUES FROM (...) TO (...)	Specify range partition values
Child table must match parent table structure	Ensure schema compatibility
Child table data must fit partition values	Avoid data conflicts

Key Takeaways

Use ALTER TABLE ... ATTACH PARTITION to link an existing table as a partition.

The child table must have the same structure and matching partition key columns.

Specify the correct FOR VALUES clause matching the partition strategy (list or range).

Ensure the child table's data fits within the specified partition values to avoid errors.

Attaching partitions helps organize data efficiently in PostgreSQL partitioned tables.