Chapter 8. Data Types
8.17.8. Deﬁning New Range Types
Users can deﬁne their own range types. The most common reason to do this is to use ranges over
subtypes not provided among the built-in range types. For example, to deﬁne a new range type of
CREATE TYPE floatrange AS RANGE (
subtype = float8,
subtype_diff = float8mi
SELECT ’[1.234, 5.678]’::floatrange;
has no meaningful “step”, we do not deﬁne a canonicalization function in this ex-
If the subtype is considered to have discrete rather than continuous values, the
mand should specify a
function.The canonicalizationfunctiontakes aninputrangevalue,
andmustreturn anequivalent range value thatmayhave different bounds andformatting. The canoni-
cal output for two ranges that representthesame setof values, for example the integer ranges
,must be identical. Itdoesn’tmatter which representation you choose to be the canonical
one, so long as two equivalent values with different formattings are always mapped to the same value
with the same formatting. In addition to adjustingthe inclusive/exclusive bounds format, a canonical-
ization function might round off boundary values, in case the desired step size is larger than what the
subtype is capable of storing. For instance, a range type over
could be deﬁned to have a
step size of an hour, in which case the canonicalization function would need to round off bounds that
weren’t a multiple of an hour, or perhaps throw an error instead.
Deﬁning your own range type also allows you to specify a different subtype B-tree operator class or
collation touse, soas to change the sort ordering that determines which values fallinto a givenrange.
In addition, any range type that is meant to be used with GiST or SP-GiST indexes should deﬁne a
subtype difference, or
,function. (the index will still work without
but it is likely to be considerably less efﬁcient than if a difference function is provided.) The subtype
difference function takes two input values of the subtype, andreturns their difference (i.e.,
represented as a
value. In our example above, the function that underlies the regular
minus operator can be used; but for any other subtype, some type conversion would be necessary.
Some creative thought about how to represent differences as numbers might be needed, too. To the
greatest extent possible, the
function should agree with the sort ordering implied
by the selected operator class and collation; that is, its result should be positive whenever its ﬁrst
argument is greater than its second according to the sort ordering.
See CREATE TYPE for more information about creating range types.
GiST and SP-GiST indexes can be created for table columns of range types. For instance, to create a
CREATE INDEX reservation_idx ON reservation USING gist (during);
AGiST or SP-GiST indexcan accelerate queries involvingthese range operators:
(see Table 9-47 for more information).
In addition, B-tree and hash indexes can be created for table columns of range types. For these index
types, basically the only useful range operation is equality. There is a B-tree sort ordering deﬁned