Range types (int4range, daterange) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of int4range containment check

What is the output of this query?

SELECT int4range(1, 5) @> 3 AS contains_three;

PostgreSQL

SELECT int4range(1, 5) @> 3 AS contains_three;

A true

B SyntaxError

C false

D null

💡 Hint

The operator @> checks if the range contains the value.

The range int4range(1,5) includes integers from 1 up to but not including 5. Since 3 is within this range, the result is true.

❓ query_result

intermediate

Result of daterange union operation

What is the result of this query?

SELECT daterange('2023-01-01', '2023-01-10') + daterange('2023-01-05', '2023-01-15') AS union_range;

PostgreSQL

SELECT daterange('2023-01-01', '2023-01-10') + daterange('2023-01-05', '2023-01-15') AS union_range;

A SyntaxError

B '[2023-01-01,2023-01-10)'

C '[2023-01-05,2023-01-15)'

D '[2023-01-01,2023-01-15)'

💡 Hint

The + operator merges overlapping or adjacent ranges.

The two dateranges overlap between 2023-01-05 and 2023-01-10, so their union is a single continuous range from 2023-01-01 to 2023-01-15 (excluding the end).

📝 Syntax

advanced

Identify the syntax error in int4range creation

Which option contains a syntax error when creating an int4range?

Query example: SELECT int4range(...);

A int4range(1, 5, '[]')

B int4range(1, 5, '[)')

C int4range(5, 1)

D )')[' ,5 ,1(egnar4tni

💡 Hint

The lower bound must be less than or equal to the upper bound.

int4range(5, 1) is invalid because the lower bound (5) is greater than the upper bound (1), causing a runtime error.

🔧 Debug

advanced

Why does this daterange overlap query return no rows?

Given a table events(date_range daterange), why does this query return no rows?

SELECT * FROM events WHERE daterange('2023-01-01', '2023-01-10') && daterange('2023-01-10', '2023-01-20');

A The ranges do not overlap because the first ends exactly at the start of the second, and ranges are half-open by default.

B The query syntax is invalid and causes no rows to be returned.

C The && operator checks for containment, not overlap.

D The daterange values are stored as text, so the operator fails silently.

💡 Hint

Remember how half-open ranges treat their boundaries.

By default, dateranges are half-open [start, end), so the first range ends at 2023-01-10 but does not include it, and the second starts at 2023-01-10. They do not overlap, so the && operator returns false.

🧠 Conceptual

expert

Choosing the correct range type for a date interval with inclusive end

You want to store a date interval that includes both the start and end dates (inclusive). Which range type and bounds option should you use in PostgreSQL?

A Use int4range with bounds '[)' and convert dates to integers.

B Use daterange with bounds '[]' to include both start and end dates.

C Use daterange with bounds '(]' to include only the end date.

D Use tsrange with default bounds '[)' for inclusive start and exclusive end.

💡 Hint

Check how bounds affect inclusion of start and end in ranges.

The daterange type with bounds '[]' includes both the start and end dates, making it suitable for fully inclusive date intervals.