Bird
0
0

Consider this setup:

medium📝 query result Q5 of 15
PostgreSQL - Table Partitioning
Consider this setup:
CREATE TABLE sales (id INT, year INT, region TEXT) PARTITION BY RANGE (year) SUBPARTITION BY LIST (region);
CREATE TABLE sales_2022 PARTITION OF sales FOR VALUES FROM (2022) TO (2023) SUBPARTITION BY LIST (region);
CREATE TABLE sales_2022_eu PARTITION OF sales_2022 FOR VALUES IN ('EU');
INSERT INTO sales VALUES (101, 2022, 'EU');

What will this query return?
SELECT * FROM sales WHERE year = 2022 AND region = 'EU';
AError due to missing partition for region
BNo rows because sub-partition is not matched
CRow with id 101 from sales_2022_eu
DRows from sales_2022 ignoring region
Step-by-Step Solution
Solution:
  1. Step 1: Check partition range for year

    Year 2022 falls into sales_2022 partition range.
  2. Step 2: Check sub-partition for region

    Region 'EU' matches sales_2022_eu sub-partition.
  3. Final Answer:

    Row with id 101 from sales_2022_eu -> Option C
  4. Quick Check:

    Partition and sub-partition keys match query [OK]
Quick Trick: Both partition and sub-partition keys must match query [OK]
Common Mistakes:
  • Ignoring sub-partition filtering
  • Expecting errors on valid keys
  • Assuming partial partition match returns rows

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More PostgreSQL Quizzes