Partition types (range, list, hash) in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  sale_date DATE NOT NULL,
  amount NUMERIC
) PARTITION BY [1] (sale_date);

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

Arange

Blist

Chash

Dcolumn

Attempts:

3 left

💡 Hint

Common Mistakes

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

Using 'column' which is not a partition type.

The range partition type divides data into ranges of values, such as dates or numbers.

2fill in blank

medium

Complete the code to create a list partitioned table by region.

PostgreSQL

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  region TEXT NOT NULL
) PARTITION BY [1] (region);

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

Alist

Brange

Chash

Dindex

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'hash' which distributes data evenly but not by specific values.

Using 'index' which is not a partition type.

The list partition type divides data by specific values, such as regions or categories.

3fill in blank

hard

Fix the error in the partition creation statement for hash partitioning.

PostgreSQL

CREATE TABLE logs (
  id SERIAL PRIMARY KEY,
  event_time TIMESTAMP NOT NULL,
  message TEXT
) PARTITION BY [1] (id);

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

Aunique

Brange

Clist

Dhash

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'unique' which is not a partition type.

Using 'range' or 'list' which are different partitioning methods.

The hash partition type distributes rows evenly across partitions based on a hash of the partition key.

4fill in blank

hard

Fill both blanks to create a range partition for sales in 2023.

PostgreSQL

CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ([1]) TO ([2]);

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

A'2023-01-01'

B'2022-12-31'

C'2024-01-01'

D'2023-12-31'

Attempts:

3 left

💡 Hint

Common Mistakes

Using end date as '2023-12-31' which excludes the last day.

Using start date before 2023.

Range partitions specify a start (inclusive) and end (exclusive) value. For 2023, start is '2023-01-01' and end is '2024-01-01'.

5fill in blank

hard

Fill all three blanks to create a list partition for customers from 'North', 'East', and 'West' regions.

PostgreSQL

CREATE TABLE customers_north_east PARTITION OF customers
FOR VALUES IN ([1], [2], [3]);

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

A'North'

B'East'

C'South'

D'West'

Attempts:

3 left

💡 Hint

Common Mistakes

Including 'South' which is not part of this partition.

Missing one of the required regions.

List partitions specify exact values. Here, we include 'North', 'East', and 'West' regions in the partition.