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

Need a 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

Need a 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

Need a 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

Need a hint?

Use the && operator to check if ranges overlap.