Design a Range Partition Table for Sales Data

Creating a Range-Partitioned Table

Write a PostgreSQL query to create a partitioned table that organizes sales data by year using range partitioning.

Solution:

-- Create a partitioned table for sales data based on the sale year.
CREATE TABLE Sales (
    -- Define an auto-incrementing primary key column named id.
    id SERIAL PRIMARY KEY,
    -- Define a non-nullable column sale_date of type DATE.
    sale_date DATE NOT NULL,
    -- Define a non-nullable column amount of type NUMERIC with precision 10 and scale 2.
    amount NUMERIC(10,2) NOT NULL
) PARTITION BY RANGE (sale_date);

Explanation:

• Purpose of the Query:
• This query sets up a partitioned table where rows are divided based on sale_date.
• It helps manage large datasets efficiently by storing data in smaller partitions.
• Key Components:
• PARTITION BY RANGE (sale_date): Defines range partitioning based on the sale_date column.
• Real-World Application:
• Useful for organizing time-series data like sales, logs, or historical records.

Notes:

• No data is stored in the parent table; partitions must be created separately.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a partitioned table for transaction logs using range partitioning on a timestamp column.
• Write a PostgreSQL query to create a partitioned table for website visits using range partitioning based on visit_date and organizing data by quarter.
• Write a PostgreSQL query to create a partitioned table for sensor readings using range partitioning on a recorded_at column with daily partitions.
• Write a PostgreSQL query to create a partitioned table for archived logs using range partitioning on the year extracted from a date column.

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

Previous PostgreSQL Exercise: Table partitioning Home.