Sub-partitioning in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a partitioned table by range on the column 'year'.

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  year INT,
  amount NUMERIC
) PARTITION BY [1] (year);

Drag options to blanks, or click blank then click option'

Alist

Brange

Ccolumn

Dhash

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'list' instead of 'range' for numeric ranges.

Using 'hash' which is for distributing data evenly, not ranges.

The PARTITION BY RANGE clause is used to partition a table based on a range of values in a column, such as 'year'.

2fill in blank

medium

Complete the code to create a sub-partitioned table by list on the column 'region'.

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  year INT,
  region TEXT,
  amount NUMERIC
) PARTITION BY range (year) SUBPARTITION BY [1] (region);

Drag options to blanks, or click blank then click option'

Ahash

Brange

Clist

Dcolumn

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'range' for categorical columns.

Using 'hash' which is for distributing data evenly.

Sub-partitioning by LIST allows dividing data into discrete categories like 'region'.

3fill in blank

hard

Fix the error in the sub-partition creation statement by choosing the correct keyword.

PostgreSQL

CREATE TABLE sales_2023 PARTITION OF sales
  FOR VALUES FROM (2023) TO (2024)
  SUBPARTITION BY [1] (region);

Drag options to blanks, or click blank then click option'

Asubpartition

Bpartition

Csubpartitioned

Dsubpartitioning

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'partition' instead of 'subpartition'.

Using incorrect forms like 'subpartitioned' or 'subpartitioning'.

The correct keyword to specify sub-partitioning is SUBPARTITION BY.

4fill in blank

hard

Fill both blanks to define a sub-partition for region 'north' in the 2023 sales partition.

PostgreSQL

CREATE TABLE sales_2023_north PARTITION OF sales_2023
  FOR VALUES IN ([1]);

ALTER TABLE sales_2023_north
  ADD CONSTRAINT [2] CHECK (region = 'north');

Drag options to blanks, or click blank then click option'

A'north'

Bnorth_region_check

C'south'

Dregion_check

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong region value like 'south'.

Using generic constraint names that don't describe the partition.

The sub-partition must specify the value 'north' and a meaningful constraint name like 'north_region_check'.

5fill in blank

hard

Fill all three blanks to create a sub-partitioned table with range partitioning on 'year' and hash sub-partitioning on 'month'.

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  year INT,
  month INT,
  amount NUMERIC
) PARTITION BY [1] (year)
SUBPARTITION BY [2] (month);

CREATE TABLE sales_2023 PARTITION OF sales
  FOR VALUES FROM (2023) TO (2024)
  SUBPARTITION BY [3] (month);

Drag options to blanks, or click blank then click option'

Arange

Bhash

Dlist

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'list' instead of 'hash' for sub-partitioning.

Mixing up partitioning types between main and sub-partitions.

The main partition uses RANGE on 'year', and sub-partitioning uses HASH on 'month'.