Organize Orders by Region with List Partitioning

Creating a List-Partitioned Table

Write a PostgreSQL query to create a partitioned table that stores orders based on region using list partitioning.

Solution:

-- Create a partitioned orders table based on region.
CREATE TABLE Orders (
    -- Define an auto-incrementing primary key column named order_id.
    order_id SERIAL PRIMARY KEY,
    -- Define a non-nullable column region of type TEXT.
    region TEXT NOT NULL,
    -- Define a non-nullable column order_amount of type NUMERIC with precision 10 and scale 2.
    order_amount NUMERIC(10,2) NOT NULL
) PARTITION BY LIST (region);

Explanation:

• Purpose of the Query:
• Uses list partitioning to separate orders by region.
• Key Components:
• PARTITION BY LIST (region): Splits data into partitions based on region values.
• Real-World Application:
• Useful for managing region-based order data in e-commerce or logistics.

Notes:

• List partitioning is best when dealing with discrete values (e.g., country names, product categories).

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a partitioned table for product reviews using list partitioning based on rating values.
• Write a PostgreSQL query to create a partitioned table for employee roles using list partitioning based on job titles.
• Write a PostgreSQL query to create a partitioned table for support tickets using list partitioning on status values.
• Write a PostgreSQL query to create a partitioned table for order types using list partitioning on predefined category values.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Creating Partitions for a Range-Partitioned Table.