Range types (int4range, daterange) in PostgreSQL - Step-by-Step Execution

Concept Flow - Range types (int4range, daterange)

Create Range Value

↓

Store Range in Column

↓

Query Range Column

↓

Check Range Operators

↓

Return Rows Matching Range Condition

↓

END

This flow shows how a range value is created, stored in a column, queried with range operators, and returns matching rows.

Execution Sample

PostgreSQL

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  event_period DATERANGE
);

INSERT INTO events (event_period) VALUES
  ('[2024-01-01,2024-01-10)'),
  ('[2024-02-01,2024-02-05)');

SELECT * FROM events WHERE event_period && '[2024-01-05,2024-01-15)';

Create a table with a daterange column, insert two date ranges, then select rows where the event_period overlaps a given date range.

Execution Table

Step	Action	Input/Condition	Result/Output
1	Create table 'events' with daterange column	N/A	Table created with column event_period of type daterange
2	Insert first event_period	'[2024-01-01,2024-01-10)'	Row inserted with event_period from Jan 1 to Jan 9 (end exclusive)
3	Insert second event_period	'[2024-02-01,2024-02-05)'	Row inserted with event_period from Feb 1 to Feb 4 (end exclusive)
4	Query events where event_period overlaps '[2024-01-05,2024-01-15)'	event_period && '[2024-01-05,2024-01-15)'	Returns first row only, because its range overlaps the query range
5	Query ends	No more rows	Query complete with 1 row returned

💡 Query ends after checking all rows; only the first row's range overlaps the query range.

Variable Tracker

Variable	Start	After Insert 1	After Insert 2	After Query
events table	empty	[{id:1, event_period:'[2024-01-01,2024-01-10)'}]	[{id:1, event_period:'[2024-01-01,2024-01-10)'}, {id:2, event_period:'[2024-02-01,2024-02-05)'}]	[{id:1, event_period:'[2024-01-01,2024-01-10)'}]

Key Moments - 2 Insights

Why does the query only return the first row and not the second?

Because the query uses the '&&' operator which checks for overlapping ranges. The first event_period '[2024-01-01,2024-01-10)' overlaps with the query range '[2024-01-05,2024-01-15)', but the second event_period '[2024-02-01,2024-02-05)' does not. See execution_table row 4.

What does the parenthesis ')' mean in the range '[2024-01-01,2024-01-10)'?

It means the range includes the start date '2024-01-01' but excludes the end date '2024-01-10'. So the range covers dates from Jan 1 up to Jan 9. This is why the overlap works as expected. See execution_table rows 2 and 4.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the second event_period inserted?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Check the 'Action' column in execution_table for the insertion steps.

According to variable_tracker, how many rows are in the events table after both inserts?

A 2

B 1

C 0

D 3

💡 Hint

Look at the 'After Insert 2' column in variable_tracker for the events table state.

If the query range was '[2024-02-03,2024-02-06)', which rows would the query return?

A Only the first row

B Only the second row

C Both rows

D No rows

💡 Hint

Consider which event_period overlaps with the new query range based on execution_table logic.

Concept Snapshot

Range types store continuous intervals like dates or integers.
int4range is for integer ranges, daterange for date ranges.
Ranges can be inclusive or exclusive at ends, e.g. '[1,5)' includes 1 but excludes 5.
Use operators like '&&' to check if ranges overlap.
Queries can filter rows based on range conditions easily.

Full Transcript

This visual execution trace shows how PostgreSQL range types like int4range and daterange work. First, a table is created with a range column. Then, rows are inserted with specific ranges. When querying, the range operators such as '&&' check if stored ranges overlap with a given range. The execution table walks through each step: creating the table, inserting rows, and querying with a range condition. The variable tracker shows how the table's data changes after each insert and query. Key moments clarify why only certain rows match the query and what the range boundaries mean. The quiz tests understanding of insertion steps, table state, and range overlap logic. The snapshot summarizes the key points about range types and their usage in queries.

Practice

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