Bird
0
0

Given the following partitioned table and partitions:

medium📝 query result Q13 of 15
PostgreSQL - Table Partitioning
Given the following partitioned table and partitions:
CREATE TABLE sales (id INT, sale_date DATE) PARTITION BY RANGE (sale_date);
CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

What will be the result of this query?
SELECT tableoid::regclass, * FROM sales WHERE sale_date = '2023-06-15';
AReturns rows from sales_2024 partition with sale_date '2023-06-15'
BReturns no rows because '2023-06-15' is not in any partition
CReturns rows from both partitions
DReturns rows from sales_2023 partition with sale_date '2023-06-15'
Step-by-Step Solution
Solution:
  1. Step 1: Identify which partition contains '2023-06-15'

    The date '2023-06-15' falls between '2023-01-01' and '2024-01-01', so it belongs to sales_2023 partition.
  2. Step 2: Understand query behavior on partitioned tables

    Query on partitioned table routes to matching partition(s) based on WHERE clause; here, only sales_2023 matches.
  3. Final Answer:

    Returns rows from sales_2023 partition with sale_date '2023-06-15' -> Option D
  4. Quick Check:

    Date in sales_2023 range = rows from sales_2023 [OK]
Quick Trick: Check date range to find correct partition for query [OK]
Common Mistakes:
  • Choosing wrong partition based on date
  • Assuming query scans all partitions
  • Ignoring partition boundaries

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More PostgreSQL Quizzes