Range types (int4range, daterange) in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a range type in PostgreSQL?

A range type in PostgreSQL represents a continuous set of values between a lower and upper bound, such as numbers or dates. It allows easy querying of intervals without storing each value separately.

beginner

What does int4range represent?

int4range is a built-in PostgreSQL range type that stores a range of 4-byte integers (normal integers). It can represent intervals like [1,10) meaning from 1 up to but not including 10.

beginner

How do you create a daterange value for dates from 2023-01-01 to 2023-01-10 inclusive?

You can write: '[2023-01-01,2023-01-10]'::daterange. The square brackets mean the range includes both start and end dates.

intermediate

How can you check if a value is inside a range in PostgreSQL?

Use the @> operator. For example, int4range(1,10) @> 5 returns true because 5 is inside the range 1 to 10.

intermediate

What is the difference between [1,10) and (1,10] in range types?

[1,10) includes 1 but excludes 10. (1,10] excludes 1 but includes 10. Square brackets mean inclusive, parentheses mean exclusive bounds.

Which PostgreSQL range type would you use to store a range of dates?

A daterange

B int4range

C textrange

D numrange

daterange is the correct type for date intervals. int4range is for integers, numrange for numeric ranges, and textrange does not exist.

What does the range notation [5,15) mean?

A Includes 5 and excludes 15

B Excludes 5 and includes 15

C Includes both 5 and 15

D Excludes both 5 and 15

Square bracket [ means inclusive, parenthesis ) means exclusive. So [5,15) includes 5 but excludes 15.

Which operator checks if a range contains a value in PostgreSQL?

A <@

B @>

C &&

D <<

The @> operator tests if the range contains the value. For example, int4range(1,10) @> 5 is true.

How do you cast a string to a range type in PostgreSQL?

A You cannot cast strings to range types

B Use <code>CAST()</code> only

C Use <code>::</code> operator, e.g. <code>'[1,5]'::int4range</code>

D Use <code>TO_RANGE()</code> function

You can cast a string to a range type using ::, like '[1,5]'::int4range.

What will daterange('2023-01-01','2023-01-10', '[]') represent?

A Dates from Jan 2 to Jan 9 inclusive

B Dates from Jan 1 to Jan 10 excluding Jan 10

C Invalid syntax

D Dates from Jan 1 to Jan 10 inclusive

The '[]' means both bounds are inclusive, so the range includes Jan 1 and Jan 10.

Explain what range types are in PostgreSQL and give examples of when you might use int4range and daterange.

Think about intervals like date bookings or number spans.

Describe how to check if a value is inside a range and how to create a range with inclusive or exclusive bounds.

Remember the symbols for inclusive and exclusive bounds.