Partition pruning behavior in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select data only from the partition where the year is 2023.

PostgreSQL

SELECT * FROM sales_[1] WHERE year = 2023;

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

A2023

B2022

C2021

D2020

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting from the wrong partition year.

Not specifying the partition at all.

To prune partitions and query only the 2023 data, you must specify the partition with year 2023.

2fill in blank

medium

Complete the query to enable partition pruning by filtering on the partition key 'region'.

PostgreSQL

SELECT * FROM sales WHERE region = [1];

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

A'North'

BNorth

C'north'

Dregion

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting quotes around string values.

Using incorrect case for the region name.

String values in SQL must be enclosed in single quotes. Using 'North' filters the partition key correctly.

3fill in blank

hard

Fix the error in the query to enable partition pruning on the 'month' column.

PostgreSQL

SELECT * FROM sales WHERE month [1] 5;

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

ABETWEEN

BLIKE

C=

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using LIKE operator on numeric columns.

Using IN without parentheses.

Partition pruning requires a direct comparison operator like '=' to filter partitions effectively.

4fill in blank

hard

Fill both blanks to create a query that prunes partitions by year and region.

PostgreSQL

SELECT * FROM sales WHERE year = [1] AND region = [2];

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

A2023

B'East'

D2022

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around the year number.

Omitting quotes around the region string.

Use 2023 for the year and 'East' (with quotes) for the region to prune partitions correctly.

5fill in blank

hard

Fill all three blanks to create a query that prunes partitions by year, region, and month.

PostgreSQL

SELECT * FROM sales WHERE year = [1] AND region = [2] AND month = [3];

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

A2024

B'West'

C7

D'7'

Attempts:

3 left

💡 Hint

Common Mistakes

Putting quotes around numeric values.

Not quoting string values.

Year and month are numbers, so no quotes. Region is a string and needs quotes.