Creating partitioned tables in PostgreSQL - Try It Yourself

Creating Partitioned Tables in PostgreSQL

📖 Scenario: You are managing a sales database for a retail company. The company wants to organize its sales data by year to improve query performance and maintenance. You will create a partitioned table in PostgreSQL to store sales records partitioned by year.

🎯 Goal: Create a partitioned table called sales partitioned by the sale_year column. Then create two partitions for the years 2023 and 2024.

📋 What You'll Learn

Create a parent table sales partitioned by RANGE on sale_year

Create a partition table sales_2023 for sales in the year 2023

Create a partition table sales_2024 for sales in the year 2024

Each partition must inherit from the parent sales table

Use correct RANGE boundaries for each partition

💡 Why This Matters

🌍 Real World

Partitioned tables help manage large datasets by splitting data into smaller, manageable parts. This improves query speed and maintenance.

💼 Career

Database administrators and backend developers often use partitioning to optimize performance and scalability of databases.

1

Create the parent partitioned table

Write a SQL statement to create a table called sales with columns id (integer), product (text), amount (numeric), and sale_year (integer). Make this table partitioned by RANGE on the sale_year column.

PostgreSQL

-- Your code here to create the partitioned table sales

Need a hint?

Use CREATE TABLE with PARTITION BY RANGE (sale_year) to define the parent table.

2

Create the 2023 partition

Write a SQL statement to create a partition table called sales_2023 for the year 2023. It should be a partition of sales with RANGE values from 2023 (inclusive) to 2024 (exclusive).

PostgreSQL

CREATE TABLE sales (
    id INTEGER,
    product TEXT,
    amount NUMERIC,
    sale_year INTEGER
) PARTITION BY RANGE (sale_year);

-- Your code here to create the sales_2023 partition

Need a hint?

Use CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM (2023) TO (2024).

3

Create the 2024 partition

Write a SQL statement to create a partition table called sales_2024 for the year 2024. It should be a partition of sales with RANGE values from 2024 (inclusive) to 2025 (exclusive).

PostgreSQL

CREATE TABLE sales (
    id INTEGER,
    product TEXT,
    amount NUMERIC,
    sale_year INTEGER
) PARTITION BY RANGE (sale_year);

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM (2023) TO (2024);

-- Your code here to create the sales_2024 partition

Need a hint?

Use CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES FROM (2024) TO (2025).

4

Add a CHECK constraint to the 2023 partition

Write a SQL statement to add a CHECK constraint named chk_sale_year_2023 on the sales_2023 partition to ensure sale_year is exactly 2023.

PostgreSQL

CREATE TABLE sales (
    id INTEGER,
    product TEXT,
    amount NUMERIC,
    sale_year INTEGER
) PARTITION BY RANGE (sale_year);

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM (2023) TO (2024);

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM (2024) TO (2025);

-- Your code here to add the CHECK constraint on sales_2023

Need a hint?

Use ALTER TABLE sales_2023 ADD CONSTRAINT chk_sale_year_2023 CHECK (sale_year = 2023).