Why partitioning is needed in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a partitioned table by specifying the partition method.

PostgreSQL

CREATE TABLE sales_data (id INT, sale_date DATE, amount NUMERIC) PARTITION BY [1] (sale_date);

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

ALIST

BINDEX

CRANGE

DHASH

Attempts:

3 left

💡 Hint

Common Mistakes

Using INDEX partitioning which is not supported in PostgreSQL.

Confusing HASH with RANGE for date columns.

The RANGE partition method divides data into ranges, which is common for date columns like sale_date.

2fill in blank

medium

Complete the code to create a partition for sales_data for the year 2023.

PostgreSQL

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

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

A'2024-01-01'

B'2022-12-31'

C'2023-12-31'

D'2023-06-30'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the last day of the year as upper bound which is inclusive.

Using a date before the partition start date.

The upper bound for the partition should be the start of the next range, so '2024-01-01' is correct.

3fill in blank

hard

Complete the code to create a partition for sales_data for the year 2024.

PostgreSQL

CREATE TABLE sales_2024 PARTITION OF sales_data FOR VALUES FROM ('2024-01-01') TO ([1]);

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

A'2024-12-31'

B'2023-12-31'

C'2024-06-30'

D'2025-01-01'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the last day of the year as upper bound which is inclusive.

Using a date before the partition start date.

The upper bound for the partition should be the start of the next range, so '2025-01-01' is correct.

4fill in blank

hard

Fill both blanks to complete the query that selects sales data only from the 2023 partition.

PostgreSQL

SELECT * FROM sales_data WHERE sale_date [1] '2023-01-01' AND sale_date [2] '2024-01-01';

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

A>=

B<

C<=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using <= for the upper bound which includes the next year.

Using < for the lower bound which excludes the first day.

The query selects sales from January 1, 2023 (inclusive) up to but not including January 1, 2024, so use >= and <.

5fill in blank

hard

Fill all three blanks to create a partitioned table by list and add a partition for region 'North'.

PostgreSQL

CREATE TABLE customer_data (id INT, region TEXT) PARTITION BY [1] (region);
CREATE TABLE customer_north PARTITION OF customer_data FOR VALUES IN ([2]);
INSERT INTO customer_north (id, region) VALUES (1, [3]);

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

ALIST

B'North'

DRANGE

Attempts:

3 left

💡 Hint

Common Mistakes

Using RANGE partitioning for discrete values.

Not quoting string values in the partition and insert.

List partitioning is used for discrete values like regions. The partition is created for the value 'North', and the insert uses the same string.