How to Create Partitioned Table in PostgreSQL: Syntax and Example

In PostgreSQL, create a partitioned table using CREATE TABLE with the PARTITION BY clause specifying the partition method like RANGE or LIST. Then create partitions using CREATE TABLE ... PARTITION OF to define each partition's data range or list.

Syntax

The basic syntax to create a partitioned table in PostgreSQL involves defining the main table with PARTITION BY clause, specifying the partition method and column. Then, individual partitions are created as child tables using PARTITION OF with specific values or ranges.

• PARTITION BY RANGE (column): Partitions data by ranges of values.
• PARTITION BY LIST (column): Partitions data by specific list of 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 the order_date column. Two partitions are created for years 2023 and 2024.

sql

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    order_date DATE NOT NULL
) 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 sample data
INSERT INTO orders (customer_name, order_date) VALUES
('Alice', '2023-05-10'),
('Bob', '2024-03-15');

-- Query data
SELECT * FROM orders ORDER BY order_date;

Output

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

Common Pitfalls

Common mistakes when creating partitioned tables include:

• Not specifying PARTITION BY in the parent table, which makes it a normal table.
• Creating partitions with overlapping ranges or lists, causing errors.
• Forgetting to create partitions after defining the parent table, leading to errors on insert.
• Using unsupported data types or expressions in partition keys.

sql

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

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

Quick Reference

Step	Description	Example
1	Create parent table with PARTITION BY clause	CREATE TABLE t (...) PARTITION BY RANGE (col);
2	Create partitions with FOR VALUES clause	CREATE TABLE t_2023 PARTITION OF t FOR VALUES FROM (...) TO (...);
3	Insert data normally into parent table	INSERT INTO t VALUES (...);
4	Query data from parent table	SELECT * FROM t;

Key Takeaways

Always define the parent table with PARTITION BY clause to enable partitioning.

Create partitions with non-overlapping ranges or lists matching the partition method.

Insert and query data through the parent table, PostgreSQL routes data to correct partitions.

Partition keys must be simple columns or expressions supported by PostgreSQL.

Check for errors on overlapping partitions or missing partitions before inserting data.