Range partitioning by date in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a partitioned table by date.

PostgreSQL

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

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

AHASH

BLIST

CRANGE

DGROUP

Attempts:

3 left

💡 Hint

Common Mistakes

Using LIST partitioning instead of RANGE for dates.

Using HASH partitioning which is for distributing data evenly.

Range partitioning is used to divide data based on ranges of values, such as dates.

2fill in blank

medium

Complete the code to create a partition for sales in 2023.

PostgreSQL

CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') [1] ('2024-01-01');

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

ATO

BINCLUSIVE

CEXCLUSIVE

DLESS

Attempts:

3 left

💡 Hint

Common Mistakes

Using EXCLUSIVE which is not valid syntax.

Using LESS which is syntax from other databases like Oracle.

The syntax uses FOR VALUES FROM ... TO ... where TO specifies the exclusive upper bound.

3fill in blank

hard

Fix the error in the partition creation by completing the missing keyword.

PostgreSQL

CREATE TABLE sales_q1_2023 PARTITION OF sales FOR VALUES [1] ('2023-01-01') TO ('2023-04-01');

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

AON

BIN

CBETWEEN

DFROM

Attempts:

3 left

💡 Hint

Common Mistakes

Using IN which is for list partitions.

Using BETWEEN which is not valid in this syntax.

The correct syntax is FOR VALUES FROM (start) TO (end) for range partitions.

4fill in blank

hard

Fill both blanks to create a partition for sales in the first half of 2024.

PostgreSQL

CREATE TABLE sales_h1_2024 PARTITION OF sales FOR VALUES [1] ('2024-01-01') [2] ('2024-07-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 BETWEEN which is not valid syntax here.

Range partitions use FROM and TO to specify the start and end of the range.

5fill in blank

hard

Fill both blanks to create a partition for sales in July 2024.

PostgreSQL

CREATE TABLE sales_july_2024 PARTITION OF sales FOR VALUES [1] ('2024-07-01') [2] ('2024-08-01');

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

AFROM

BTO

CEXCLUSIVE

DINCLUSIVE

Attempts:

3 left

💡 Hint

Common Mistakes

Using INCLUSIVE which is not valid here.

Omitting the FROM keyword.

The syntax uses FROM and TO to define the range, and TO is exclusive by default.