Set Up Year-Based Partitions for Sales Table

Creating Partitions for a Range-Partitioned Table

Write a PostgreSQL query to create partitions for the Sales table, storing sales data for the years 2023 and 2024.

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:
• Creates partitions for storing sales data separately for each year.
• Key Components:
• FOR VALUES FROM ... TO ...: Specifies the date range for each partition.
• Real-World Application:
• Helps in managing historical data by keeping older records in separate tables.

Notes:

• New partitions must be added manually for upcoming years.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create partitions for a Range-Partitioned table covering transaction data for 2022, 2023, and 2024.
• Write a PostgreSQL query to create a partition for a Range-Partitioned table specifically for data occurring on leap day.
• Write a PostgreSQL query to create a partition for a Range-Partitioned table that covers Q1 of 2023.
• Write a PostgreSQL query to create monthly partitions for a Range-Partitioned table for the entire year of 2023.

Go to:

• Comprehensive Guide to Table Partitioning in PostgreSQL Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : Creating a Range-Partitioned Table.
NEXT : Creating a List-Partitioned Table.

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