Bird
Raised Fist0
PostgreSQLquery~5 mins

Range types (int4range, daterange) in PostgreSQL - Time & Space Complexity

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Time Complexity: Range types (int4range, daterange)
O(n)
Understanding Time Complexity

When working with range types like int4range or daterange in PostgreSQL, it's important to understand how the time to process queries grows as the data size increases.

We want to know how the number of operations changes when we search or filter using these range types.

Scenario Under Consideration

Analyze the time complexity of this query using a range type filter.


SELECT *
FROM events
WHERE event_date && daterange('2024-01-01', '2024-12-31', '[]');

This query selects all rows where the event_date range overlaps with the year 2024.

Identify Repeating Operations

Look for repeated checks or scans in the query.

  • Primary operation: Checking each row's event_date range for overlap.
  • How many times: Once per row in the events table.
How Execution Grows With Input

As the number of rows grows, the database must check more ranges for overlap.

Input Size (n)Approx. Operations
1010 overlap checks
100100 overlap checks
10001000 overlap checks

Pattern observation: The number of checks grows directly with the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows linearly with the number of rows in the table.

Common Mistake

[X] Wrong: "Using range types automatically makes queries run in constant time."

[OK] Correct: The database still needs to check each row unless an index is used, so the time grows with the number of rows.

Interview Connect

Understanding how range queries scale helps you explain query performance clearly and shows you know how databases handle special data types.

Self-Check

What if we added a GiST index on the event_date column? How would the time complexity change?

Practice

(1/5)
1. What does the PostgreSQL int4range data type store?
easy
A. A range of 4-byte floating point numbers
B. A range of 4-byte integers
C. A range of dates
D. A range of text strings

Solution

  1. Step 1: Understand the name int4range

    The prefix int4 means 4-byte integer in PostgreSQL.

  2. Step 2: Identify the stored data type

    int4range stores a range (interval) of 4-byte integers.

  3. Final Answer:

    A range of 4-byte integers -> Option B

  4. Quick Check:

    int4range = integer range [OK]
Hint: Remember int4 means 4-byte integer range [OK]
Common Mistakes:
  • Confusing int4range with floating point ranges
  • Thinking it stores dates instead of integers
  • Assuming it stores text ranges
2. Which of the following is the correct syntax to create a table with a column named period of type daterange?
easy
A. CREATE TABLE events (period daterange[]);
B. CREATE TABLE events (period range(daterange));
C. CREATE TABLE events (period daterange);
D. CREATE TABLE events (period date range);

Solution

  1. Step 1: Recall correct type declaration

    In PostgreSQL, range types like daterange are used directly as column types.

  2. Step 2: Check each option

    CREATE TABLE events (period daterange); uses period daterange which is correct syntax. CREATE TABLE events (period range(daterange)); wrongly uses range(daterange). CREATE TABLE events (period date range); splits the type incorrectly. CREATE TABLE events (period daterange[]); declares an array, not a single range.

  3. Final Answer:

    CREATE TABLE events (period daterange); -> Option C

  4. Quick Check:

    Use type name directly for range columns [OK]
Hint: Use range type name directly as column type [OK]
Common Mistakes:
  • Adding extra parentheses around range type
  • Splitting type name into two words
  • Using array syntax when not needed
3. Given the table bookings with a daterange column stay, what does this query return?
SELECT * FROM bookings WHERE stay && daterange('2024-06-01', '2024-06-10');
medium
A. Rows where stay starts exactly on June 1, 2024
B. Rows where stay is completely inside June 1 to June 10, 2024
C. Rows where stay ends before June 10, 2024
D. Rows where stay overlaps with June 1 to June 10, 2024

Solution

  1. Step 1: Understand the operator && for ranges

    The && operator checks if two ranges overlap at all.

  2. Step 2: Analyze the query condition

    The query selects rows where the stay range overlaps with the range from June 1 to June 10, 2024.

  3. Final Answer:

    Rows where stay overlaps with June 1 to June 10, 2024 -> Option D

  4. Quick Check:

    Range overlap operator && means any overlap [OK]
Hint: && means any overlap between ranges [OK]
Common Mistakes:
  • Thinking && means fully inside
  • Confusing overlap with exact start or end match
  • Assuming it filters only before or after dates
4. You wrote this query to find bookings fully inside June 2024:
SELECT * FROM bookings WHERE stay <@ daterange('2024-06-01', '2024-06-30');

But it returns no rows even though some bookings are inside June. What is the likely problem?
medium
A. The daterange upper bound is exclusive by default, so '2024-06-30' is not included
B. The <@ operator checks for overlap, not containment
C. The stay column is not of type daterange
D. The query syntax is invalid and causes an error

Solution

  1. Step 1: Understand <@ operator meaning

    <@ means the left range is fully contained inside the right range.

  2. Step 2: Check daterange bounds behavior

    By default, daterange upper bound is exclusive, so '2024-06-30' is not included in the range. This excludes bookings on June 30.

  3. Final Answer:

    The daterange upper bound is exclusive by default, so '2024-06-30' is not included -> Option A

  4. Quick Check:

    Upper bound exclusive means end date not included [OK]
Hint: Remember daterange upper bound is exclusive by default [OK]
Common Mistakes:
  • Confusing <@ with overlap operator
  • Assuming inclusive upper bound by default
  • Ignoring data type mismatch
5. You want to find all integer ranges in a table intervals with column int_range (type int4range) that do NOT overlap with [10, 20). Which query correctly finds these non-overlapping ranges?
hard
A. SELECT * FROM intervals WHERE NOT (int_range && int4range(10, 20));
B. SELECT * FROM intervals WHERE int_range && int4range(10, 20);
C. SELECT * FROM intervals WHERE int_range <@ int4range(10, 20);
D. SELECT * FROM intervals WHERE int_range @> int4range(10, 20);

Solution

  1. Step 1: Understand the overlap operator &&

    && returns true if ranges overlap.

  2. Step 2: Find non-overlapping ranges

    To find ranges that do NOT overlap, negate the overlap condition with NOT (int_range && int4range(10, 20)).

  3. Final Answer:

    SELECT * FROM intervals WHERE NOT (int_range && int4range(10, 20)); -> Option A

  4. Quick Check:

    Negate overlap to find non-overlapping ranges [OK]
Hint: Use NOT with && to find non-overlapping ranges [OK]
Common Mistakes:
  • Using && without NOT to find non-overlapping
  • Confusing <@ and @> operators
  • Using containment instead of overlap