List partitioning by category in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of inserting data into a category partitioned table

Given a PostgreSQL table products partitioned by LIST on the category column, what will be the output of the following query?

INSERT INTO products (id, name, category) VALUES (1, 'Apple', 'Fruit') RETURNING *;

Assume partitions exist for categories 'Fruit' and 'Vegetable'.

PostgreSQL

CREATE TABLE products (id INT, name TEXT, category TEXT) PARTITION BY LIST (category);
CREATE TABLE products_fruit PARTITION OF products FOR VALUES IN ('Fruit');
CREATE TABLE products_vegetable PARTITION OF products FOR VALUES IN ('Vegetable');

A 1 | Apple | NULL

B Error: no partition for category 'Fruit'

C 1 | Apple | Fruit

D 1 | Apple | Vegetable

💡 Hint

Check if the partition for the category exists before inserting.

Since a partition for 'Fruit' exists, the row is inserted successfully and returned.

🧠 Conceptual

intermediate

Understanding list partitioning behavior

Which statement best describes how PostgreSQL routes data when using LIST partitioning by category?

A Data is routed randomly to any partition regardless of the key value.

B Data is routed to partitions based on matching the partition key value exactly in the partition's value list.

C Data is routed to partitions based on range intervals of the key value.

D Data is routed to the default partition only.

💡 Hint

Think about how LIST partitioning differs from RANGE partitioning.

LIST partitioning routes rows to partitions where the partition key matches one of the listed values exactly.

📝 Syntax

advanced

Correct syntax for creating a list partitioned table

Which of the following SQL statements correctly creates a table orders partitioned by LIST on the order_type column with partitions for 'Online' and 'InStore'?

A

CREATE TABLE orders (id INT, order_type TEXT) PARTITION BY LIST (order_type);
CREATE TABLE orders_online PARTITION OF orders FOR VALUES IN ('Online');
CREATE TABLE orders_instore PARTITION OF orders FOR VALUES IN ('InStore');

B

CREATE TABLE orders (id INT, order_type TEXT) PARTITION BY RANGE (order_type);
CREATE TABLE orders_online PARTITION OF orders FOR VALUES IN ('Online');
CREATE TABLE orders_instore PARTITION OF orders FOR VALUES IN ('InStore');

C

CREATE TABLE orders (id INT, order_type TEXT) PARTITION BY LIST (order_type);
CREATE TABLE orders_online PARTITION OF orders FOR VALUES FROM ('Online') TO ('Online');
CREATE TABLE orders_instore PARTITION OF orders FOR VALUES FROM ('InStore') TO ('InStore');

D

CREATE TABLE orders (id INT, order_type TEXT) PARTITION BY LIST (order_type);
CREATE TABLE orders_online PARTITION OF orders FOR VALUES IN ('Online', 'InStore');

💡 Hint

Check the partitioning method and the syntax for partition value lists.

Option A correctly uses LIST partitioning and defines partitions with FOR VALUES IN (value).

❓ optimization

advanced

Optimizing queries on list partitioned tables

You have a large sales table partitioned by LIST on region. Which query will most efficiently use partition pruning to scan only the 'North' region partition?

A SELECT * FROM sales WHERE region <> 'North';

B SELECT * FROM sales WHERE region LIKE '%North%';

C SELECT * FROM sales WHERE region IN ('North', 'South');

D SELECT * FROM sales WHERE region = 'North';

💡 Hint

Partition pruning works best with exact matches on the partition key.

Only the query with an exact equality condition on the partition key allows pruning to scan just the 'North' partition.

🔧 Debug

expert

Diagnosing insert failure on list partitioned table

Given a table employees partitioned by LIST on department with partitions for 'HR' and 'IT', the following insert fails:

INSERT INTO employees (id, name, department) VALUES (10, 'John Doe', 'Finance');

What is the most likely cause of this error?

A No partition exists for the 'Finance' department, so the insert fails.

B The 'Finance' value is misspelled in the insert statement.

C The table is partitioned by RANGE, not LIST, causing the error.

D The 'department' column is missing from the table definition.

💡 Hint

Check if a partition exists for the inserted category value.

In LIST partitioning, inserting a value not covered by any partition causes an error.