How to Create Partition in PostgreSQL: Syntax and Example

In PostgreSQL, you create partitions by defining a partitioned table with PARTITION BY clause and then creating child tables as partitions using FOR VALUES. This helps split large tables into smaller, manageable pieces based on a key like range or list.

Syntax

To create a partitioned table, use CREATE TABLE with PARTITION BY clause specifying the partition method (e.g., RANGE, LIST, or HASH). Then create partitions as child tables with FOR VALUES defining the partition boundaries.

• PARTITION BY RANGE (column): partitions data by ranges of values.
• PARTITION BY LIST (column): partitions data by specific values.
• PARTITION BY HASH (column): partitions data by hashing values.

sql

CREATE TABLE parent_table (
  id INT,
  data TEXT,
  created_date DATE
) PARTITION BY RANGE (created_date);

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

CREATE TABLE parent_table_2024 PARTITION OF parent_table
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Example

This example creates a partitioned table orders partitioned by range on order_date. Two partitions are created for years 2023 and 2024.

sql

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_name TEXT,
  order_date DATE
) PARTITION BY RANGE (order_date);

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

CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

INSERT INTO orders (customer_name, order_date) VALUES
  ('Alice', '2023-05-10'),
  ('Bob', '2024-03-15');

SELECT * FROM orders;

Output

 order_id | customer_name | order_date 
----------+---------------+------------
        1 | Alice         | 2023-05-10 
        2 | Bob           | 2024-03-15 

Common Pitfalls

Common mistakes when creating partitions include:

• Not specifying PARTITION BY in the parent table, which disables partitioning.
• Overlapping partition ranges causing errors.
• Forgetting to create partitions before inserting data, leading to errors.
• Using unsupported data types or expressions in partition keys.

sql

/* Wrong: No PARTITION BY clause */
CREATE TABLE sales (
  id INT,
  sale_date DATE
);

/* Right: With PARTITION BY RANGE */
CREATE TABLE sales (
  id INT,
  sale_date DATE
) PARTITION BY RANGE (sale_date);

Quick Reference

Clause	Description
PARTITION BY RANGE (column)	Partition table by ranges of column values
PARTITION BY LIST (column)	Partition table by specific list of values
PARTITION BY HASH (column)	Partition table by hash of column values
FOR VALUES FROM ... TO ...	Define range boundaries for a partition
FOR VALUES IN (...)	Define list of values for a partition

Key Takeaways

Always define the parent table with PARTITION BY clause before creating partitions.

Create partitions with FOR VALUES to specify data ranges or lists.

Avoid overlapping partition ranges to prevent errors.

Insert data only after partitions are created to avoid insertion errors.

Use appropriate partition method (RANGE, LIST, HASH) based on your data.