Range types (int4range, daterange) in PostgreSQL - Mini Project: Build & Apply

Working with Range Types in PostgreSQL

📖 Scenario: You are managing a library system that tracks book loans. Each loan has a start date and an end date. You want to store these dates efficiently and check if a book is currently loaned out during a specific date range.

🎯 Goal: Create a table using PostgreSQL range types to store loan periods, insert sample data, and query loans that overlap with a given date range.

📋 What You'll Learn

Create a table called book_loans with columns loan_id (integer) and loan_period of type daterange

Insert three rows with specific loan periods using daterange literals

Create a variable check_period as a daterange to represent the date range to check

Write a query to select loan_id and loan_period from book_loans where loan_period overlaps with check_period

💡 Why This Matters

🌍 Real World

Range types are useful for storing intervals like dates, times, or numbers efficiently and querying overlaps, gaps, or containment.

💼 Career

Many database roles require understanding range types to optimize queries for scheduling, booking, or inventory systems.

1

Create the book_loans table with a daterange column

Write a SQL statement to create a table called book_loans with two columns: loan_id as an integer primary key, and loan_period as a daterange type.

PostgreSQL

-- Create the book_loans table with loan_id and loan_period columns
-- Your code here

Hint

Use CREATE TABLE with loan_id INT PRIMARY KEY and loan_period DATERANGE.

2

Insert sample loan periods using daterange literals

Insert three rows into book_loans with loan_id values 1, 2, and 3. Use these exact loan periods as daterange literals: '[2024-01-01,2024-01-10)', '[2024-01-05,2024-01-15)', and '[2024-01-20,2024-01-25)'.

PostgreSQL

CREATE TABLE book_loans (
    loan_id INT PRIMARY KEY,
    loan_period DATERANGE
);

-- Insert three rows with loan_id and loan_period using daterange literals
-- Your code here

Hint

Use INSERT INTO book_loans (loan_id, loan_period) VALUES with the exact daterange literals.

3

Create a check_period variable as a daterange

Write a SQL statement to define a variable called check_period as the daterange '[2024-01-08,2024-01-22)'. Use a WITH clause to define this variable.

PostgreSQL

CREATE TABLE book_loans (
    loan_id INT PRIMARY KEY,
    loan_period DATERANGE
);

INSERT INTO book_loans (loan_id, loan_period) VALUES
(1, '[2024-01-01,2024-01-10)'),
(2, '[2024-01-05,2024-01-15)'),
(3, '[2024-01-20,2024-01-25)');

-- Define check_period as daterange '[2024-01-08,2024-01-22)' using WITH
-- Your code here

Hint

Use a WITH clause and cast the string to daterange with ::daterange.

4

Query loans overlapping with check_period

Complete the SQL query to select loan_id and loan_period from book_loans where loan_period overlaps with check_period.period. Use the && operator inside the WITH check_period clause from Step 3.

PostgreSQL

CREATE TABLE book_loans (
    loan_id INT PRIMARY KEY,
    loan_period DATERANGE
);

INSERT INTO book_loans (loan_id, loan_period) VALUES
(1, '[2024-01-01,2024-01-10)'),
(2, '[2024-01-05,2024-01-15)'),
(3, '[2024-01-20,2024-01-25)');

WITH check_period AS (
    SELECT '[2024-01-08,2024-01-22)'::daterange AS period
)

-- Select loan_id and loan_period where loan_period overlaps check_period.period
-- Your code here

Hint

Use the && operator to check if ranges overlap.

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