Range Types
It's no secret that PostgreSQL is one of the most flexible databases on the market. In fact, PostgreSQL's extensibility and rich feature set recently propelled PostgreSQL ahead of MySQL as the most admired and desired database system among developers. In this series on creating custom data types in PostgreSQL using Navicat Premium 17 we've explored a few options so far, including custom Domains, as well as Composite and Enumerated types. The topic of this week's blog will be Range types, which are particularly useful when you need to work with continuous intervals or ranges of values.
A Quick Description of the RANGE TYPE
Range Types in PostgreSQL provide a means for working with continuous intervals of values. Hence, a range could include all product prices between $10 and $20. These ranges let you work with any values that fall within their bounds, making it easy to check for things like scheduling conflicts or price matching. Ranges are particularly useful in databases when you need to work with continuous spans of time, numerical intervals, or any other sequential data.
For example, in a movie theater's database, you might use ranges to represent screening times, ensuring no two movies are scheduled to overlap in the same theater. Or in a hotel booking system, ranges could track room availability dates, making it easy to check for vacancy conflicts. Range types are especially valuable because PostgreSQL handles all the complex logic of comparing and manipulating these intervals, providing built-in operations to check for overlaps, containment, and intersections between ranges.
In the next section, we'll go over a couple of practical examples using Navicat Premium 17 and the free DVD Rental database.
Defining Film Runtime Ranges
Before considering a custom range type, we should check is one of PostgreSQL's built-in range types would accomplish what we're looking for. These include:
- int4range: Range of integer
- int8range: Range of bigint
- numrange: Range of numeric
- tsrange: Range of timestamp without time zone
- tstzrange: Range of timestamp with time zone
- daterange: Range of date
Although film runtimes in the DVD Rental database are stored as integers, creating our own range type makes sense when we have specific business requirements that aren't covered by built-in types. For instance, if we were tracking film runtime ranges with special validation rules:
-- Creating a custom minutes range type with specific validation
CREATE TYPE runtime_range AS RANGE (
subtype = integer,
subtype_diff = int4mi
);
CREATE TABLE film_runtime_categories (
category_name VARCHAR(50),
typical_runtime runtime_range,
CHECK (lower(typical_runtime) >l= 30 AND upper(typical_runtime) <= 240)
);
-- Adding rows to the table
INSERT INTO film_runtime_categories VALUES
('Short Film', '[30,45]');
INSERT INTO film_runtime_categories VALUES
('Feature Film', '[75,180]');
Creating a Range Type in Navicat 17
An easier way to define a custom Type is to use Navicat's GUI-based tools. You'll find them in both Navicat Premium 17 and Navicat for PostgreSQL 17. To access the Type tool, simply click "Others" in the main toolbar and then select "Type" from the drop-down:
That will bring up the Objects pane, where we'll see a list of existing types. To create a new one, click on the arrow next to the "New Type" item in the "Objects" toolbar and select the "Range" item from the context menu:
The Range Type designer has three tabs: General, Comment, and SQL Preview. On the General tab, the main details that we need to supply are the "Subtype" and "Subtype Diff". We'll base our type on the int4 as follows:
Before clicking the "Save" button we can take a look at the statement that Navicat will generate by clicking on the "SQL Preview" tab:
Notice that the Type name is "Untitled" since we haven't yet saved the definition. That is expected.
Upon clicking on the "Save" button, we are presented with a "Save As" dialog where we can give our Type a name. Let's call it "runtime_range":
We can now use our "runtime_range" type just like any other PostgreSQL data type. For instance, if we create the "film_runtime_categories" table that we saw in the example above, we can set the "typical_runtime" column to our custom type by selecting it from the "Object Type" drop-down(s):
We can then add our field validation on the Checks tab:
Conclusion
In today's blog, we created a Range Type using Navicat Premium 17's Type tool and created a new table that featured our custom type. In Part 5 we will conclude the series by extending the Base Type.
