Range types

PostgreSQL is one of the most flexible databases on the market, and it's no secret. In fact, the scalability and rich feature set of PostgreSQL have recently surpassed MySQL to become the most respected and popular database system among developers. In this series of using Navicat Premium 17 to create custom data types in PostgreSQL, we have explored some options, including custom fields, composite types, and enumeration types. The theme of this week's blog is range types, which are particularly useful when dealing with continuous intervals or numerical ranges.

Brief description of scope type

The range type in PostgreSQL provides a method for handling continuous interval values. Therefore, a range can include all product prices between $10 and $20. Through these ranges, you can handle any value within their range, making it easy to check for issues such as plan conflicts or price matches. Range is particularly useful in databases when dealing with continuous time spans, numerical intervals, or any other continuous data.

For example, in a cinema's database, you can use range to represent screening times, ensuring that no two movies are re screened in the same cinema. Alternatively, in the hotel booking system, the range can track room availability dates for easy checking of room conflicts. Range types are particularly useful because PostgreSQL can handle the complex logic of comparing and manipulating these ranges, providing built-in operations to check for overlap, inclusion, and crossover between ranges.

Define the time range for movie screenings

Before considering custom scope types, we should confirm whether PostgreSQL's built-in scope types can achieve our goals. These types include:

int4range： Integer range
Int8range: the range of bigint
numrange： Numerical Range
tsrange: Timestamp range without time zone
tstzrange： Timestamp range including time zone
daterange： Date Range

Although the movie screening times in the DVD Rental database are stored in integer form, it still makes sense to create our own range types when we have specific business needs that cannot be met by built-in types. For example, if we want to track the screening time range of movies with special validation rules:

Creating Range Types in Navicat 17

A simpler way to define custom types is to use Navicat's graphical user interface based tools. You can find them in Navicat Premium 17 and Navicat for PostgreSQL 17. To access the Type Tool, simply click on "Other" in the main toolbar and select "Type" from the drop-down menu:

This will bring up the 'Objects' pane, where we will see a list of existing types. To create a new type, click the arrow next to the "New Type" item in the "Objects" toolbar, and then select the "Range" item from the context menu:

The Scope Type Designer has three tabs: General, Annotations, and SQL Preview. On the General tab, the main information we need to provide is "Subtypes" and "Subtype Differences". Our type will be based on int4, as shown below:

Before clicking the 'Save' button, we can view the statements generated by Navicat by clicking on the 'SQL Preview' tab:

Note that the type name is' Untitled 'because we have not saved the definition yet. This is expected.

After clicking the "Save" button, we will see a "Save As" dialog box, where we can name the type "runtime_range":

Now, we can use the 'runtime_range' type just like other PostgreSQL data types. For example, if we create the "film_rruntime_categories" table in the example above, we can select the "typifier_runtime" column from the "Object Type" drop-down menu and set it to our custom type:

Then, we can add field validation on the "Check" tab: