Range types (int4range, daterange) in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create an integer range from 1 to 10 (excluding 10).

PostgreSQL

SELECT int4range(1, [1]);

Drag options to blanks, or click blank then click option'

A11

B9

C10

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 11 as upper bound will create a range up to 10 inclusive, which is not the default behavior.

Using 9 will exclude 9 and 10, which is too small.

The int4range function creates a range of integers. The upper bound 10 is excluded by default, so int4range(1, 10) means from 1 up to but not including 10.

2fill in blank

medium

Complete the code to create a date range from January 1, 2023 to January 10, 2023 (excluding the end date).

PostgreSQL

SELECT daterange('2023-01-01', [1]);

Drag options to blanks, or click blank then click option'

A'2023-01-09'

B'2023-01-10'

C'2023-01-11'

D'2023-01-01'

Attempts:

3 left

💡 Hint

Common Mistakes

Using January 9 as end date excludes January 9 itself.

Using January 11 includes January 10, which is beyond the intended range.

The daterange function creates a date range. The end date is exclusive by default, so to include January 9, the end date should be January 10.

3fill in blank

hard

Fix the error in the code to check if the integer 5 is inside the range from 1 to 10.

PostgreSQL

SELECT 5 [1] int4range(1, 10);

Drag options to blanks, or click blank then click option'

A<@

BCONTAINS

C@>

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using IN is invalid syntax for range containment.

Using CONTAINS is not a valid SQL operator.

Using @> reverses the operands.

The operator <@ checks if the left value is contained in the right range. So 5 <@ int4range(1, 10) returns true if 5 is inside the range.

4fill in blank

hard

Fill both blanks to create a range from 5 to 15 including 15.

PostgreSQL

SELECT int4range([1], [2], '];');

Drag options to blanks, or click blank then click option'

A5

B15

C16

D14

Attempts:

3 left

💡 Hint

Common Mistakes

Using 15 as upper bound excludes 15 even with '];' bounds.

Using 14 excludes 15.

The int4range function takes lower and upper bounds. The '];' means the range includes the upper bound. To include 15, the upper bound must be 16 because the upper bound is exclusive by default unless specified.

5fill in blank

hard

Fill all three blanks to create a daterange from March 1, 2023 to March 31, 2023 including both dates.

PostgreSQL

SELECT daterange([1], [2], [3]);

Drag options to blanks, or click blank then click option'

A'2023-03-01'

B'2023-04-01'

C'[]'

D'[)'

Attempts:

3 left

💡 Hint

Common Mistakes

Using '()' or '[)' excludes the end date.

Using March 31 as end date excludes March 31.

The daterange function takes start date, end date, and bounds. To include both start and end dates, use bounds '[]'. The end date must be one day after March 31, which is April 1.

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