Extend Sales Table with a New 2025 Partition

Adding a New Partition to an Existing Table

Write a PostgreSQL query to add a new partition to store sales data for the year 2025 in a range-partitioned table.

Solution:

-- Create a new partition for sales data in 2025.
CREATE TABLE Sales_2025 PARTITION OF Sales
-- Define the partition to include sale_date values from January 1, 2025 to January 1, 2026.
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

Explanation:

• Purpose of the Query:
• Adds a new partition to an existing partitioned table to store 2025 sales data.
• Key Components:
• FOR VALUES FROM ... TO ...: Defines the range of dates included in this partition.
• Real-World Application:
• Commonly used in time-based partitioning, ensuring continuous data storage for future periods.

Notes:

• o Make sure the parent table (Sales) is partitioned by range.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to add a new partition for a specific month (e.g., March 2025) in a range-partitioned table.
• Write a PostgreSQL query to add a new partition for a custom date range that does not align with full years.
• Write a PostgreSQL query to add a new partition with a check constraint on the partitioning column.
• Write a PostgreSQL query to add a new partition that covers a dynamic date range based on current date calculations.

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

Previous PostgreSQL Exercise: Managing partitioned tables Home.