Creating partitioned tables in PostgreSQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

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

PostgreSQL

CREATE TABLE orders (
  order_id SERIAL,
  created_date DATE NOT NULL,
  amount NUMERIC(10, 2) NOT NULL,
  PRIMARY KEY (created_date, order_id)
) PARTITION BY [1] (created_date);

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

Acolumn

Blist

Chash

Drange

Attempts:

3 left

💡 Hint

Common Mistakes

Using PARTITION BY LIST instead of RANGE.

Using PARTITION BY COLUMN which is not valid syntax.

The PARTITION BY RANGE clause creates partitions based on ranges of values in the specified column.

2fill in blank

medium

Complete the code to create a partition for the 'orders' table for dates before 2023-01-01.

PostgreSQL

CREATE TABLE orders_2022 PARTITION OF orders
FOR VALUES FROM (MINVALUE) [1] ('2023-01-01');

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

ABEFORE

BTO

CIN

DFROM

Attempts:

3 left

💡 Hint

Common Mistakes

Using FROM instead of TO for upper bound.

Using IN which is for list partitions.

The FOR VALUES FROM (MINVALUE) TO clause defines the upper bound for the range partition.

3fill in blank

hard

Fix the error in the partition creation by choosing the correct keyword for the range start.

PostgreSQL

CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES [1] ('2023-01-01') TO ('2024-01-01');

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

AFROM

BIN

CTO

DBETWEEN

Attempts:

3 left

💡 Hint

Common Mistakes

Using IN which is for list partitions.

Using TO instead of FROM for the lower bound.

The FROM keyword specifies the lower bound of the range partition.

4fill in blank

hard

Fill both blanks to create a list partition for the 'orders' table for specific regions.

PostgreSQL

CREATE TABLE orders (
  order_id SERIAL,
  region TEXT NOT NULL,
  amount NUMERIC(10, 2) NOT NULL,
  PRIMARY KEY (region, order_id)
) PARTITION BY [1] (region);

CREATE TABLE orders_us PARTITION OF orders
FOR VALUES [2] ('US', 'Canada');

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

Alist

Brange

CIN

DTO

Attempts:

3 left

💡 Hint

Common Mistakes

Using RANGE instead of LIST for specific values.

Using TO instead of IN for list values.

PARTITION BY LIST is used to partition by specific values, and FOR VALUES IN specifies those values.

5fill in blank

hard

Fill all three blanks to create a hash partitioned table with 4 partitions.

PostgreSQL

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username TEXT NOT NULL
) PARTITION BY [1] (user_id);

CREATE TABLE users_part_1 PARTITION OF users FOR VALUES WITH ([2] 4, [3] 0);

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

AHASH

BREMAINDER

DMODULUS

Attempts:

3 left

💡 Hint

Common Mistakes

Using RANGE or LIST instead of HASH.

Confusing MODULUS and REMAINDER keywords.

PARTITION BY HASH partitions data by hashing the column. The MODULUS defines the number of partitions, and REMAINDER specifies the partition number.