# Exercises – Partitioning Tables

Lesson Progress
0% Complete
Topic
Materials

Here is the exercise to get comfort with partitioning. We will be using range partitioning.

• Use retail database. Make sure orders table already exists.

• You can reset the database by running these commands.

• Connect to retail database.

psql -U itversity_retail_user
-h localhost
-p 5432
-d itversity_retail_db
-W


• Run these commands or scripts to reset the tables. It will take care of recreating orders table.

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS departments;

i /data/retail_db/create_db_tables_pg.sql



## Exercise 1¶

Create table orders_part with the same columns as orders.

• Partition the table by month using range partitioning on order_date.

• Add 14 partitions - 13 based up on the data and 1 default. Here is the naming convention.

• Default - orders_part_default

• Partition for 2014 January - orders_part_201401

## Exercise 2¶

Let us load and validate data in the partitioned table.

• Load the data from orders into orders_part.

• Get count on orders_part as well as all the 14 partitions. You should get 0 for default partition and all the records should be distributed using the other 13 partitions.