This module implements a data type seg
for representing line segments, or floating
point intervals. seg
can represent
uncertainty in the interval endpoints, making it especially
useful for representing laboratory measurements.
The geometry of measurements is usually more complex than that of a point in a numeric continuum. A measurement is usually a segment of that continuum with somewhat fuzzy limits. The measurements come out as intervals because of uncertainty and randomness, as well as because the value being measured may naturally be an interval indicating some condition, such as the temperature range of stability of a protein.
Using just common sense, it appears more convenient to store such data as intervals, rather than pairs of numbers. In practice, it even turns out more efficient in most applications.
Further along the line of common sense, the fuzziness of the limits suggests that the use of traditional numeric data types leads to a certain loss of information. Consider this: your instrument reads 6.50, and you input this reading into the database. What do you get when you fetch it? Watch:
test=> select 6.50 :: float8 as "pH"; pH  6.5 (1 row)
In the world of measurements, 6.50 is not the same as 6.5. It may sometimes be critically different. The experimenters usually write down (and publish) the digits they trust. 6.50 is actually a fuzzy interval contained within a bigger and even fuzzier interval, 6.5, with their center points being (probably) the only common feature they share. We definitely do not want such different data items to appear the same.
Conclusion? It is nice to have a special data type that can record the limits of an interval with arbitrarily variable precision. Variable in the sense that each data element records its own precision.
Check this out:
test=> select '6.25 .. 6.50'::seg as "pH"; pH  6.25 .. 6.50 (1 row)
The external representation of an interval is formed using
one or two floatingpoint numbers joined by the range operator
(..
or ...
). Alternatively, it can be specified as a
center point plus or minus a deviation. Optional certainty
indicators (<
, >
or ~
) can be
stored as well. (Certainty indicators are ignored by all the
builtin operators, however.) Table F.26
gives an overview of allowed representations; Table F.27
shows some examples.
In Table F.26,
x
, y
, and delta
denote floatingpoint
numbers. x
and
y
, but not delta
, can be preceded by a
certainty indicator.
Table F.26. seg
External Representations

Single value (zerolength interval) 

Interval from x to y 

Interval from x  delta to x + delta 

Open interval with lower bound x 
.. 
Open interval with upper bound x 
Table F.27. Examples of
Valid seg
Input
5.0 
Creates a zerolength segment (a point, if you will) 
~5.0 
Creates a zerolength segment and records
~ in the data.
~ is ignored by
seg operations, but is
preserved as a comment. 
<5.0 
Creates a point at 5.0. < is ignored but is preserved as
a comment. 
>5.0 
Creates a point at 5.0. > is ignored but is preserved as
a comment. 
5(+)0.3 
Creates an interval 4.7 ..
5.3 . Note that the (+) notation isn't preserved. 
50 .. 
Everything that is greater than or equal to 50 
.. 0 
Everything that is less than or equal to 0 
1.5e2 .. 2E2 
Creates an interval 0.015
.. 0.02 
1 ... 2 
The same as 1...2 ,
or 1 .. 2 , or
1..2 (spaces around the
range operator are ignored) 
Because ...
is widely used in
data sources, it is allowed as an alternative spelling of
..
. Unfortunately, this creates a
parsing ambiguity: it is not clear whether the upper bound in
0...23
is meant to be 23
or 0.23
. This
is resolved by requiring at least one digit before the decimal
point in all numbers in seg
input.
As a sanity check, seg
rejects
intervals with the lower bound greater than the upper, for
example 5 .. 2
.
seg
values are stored internally
as pairs of 32bit floating point numbers. This means that
numbers with more than 7 significant digits will be
truncated.
Numbers with 7 or fewer significant digits retain their original precision. That is, if your query returns 0.00, you will be sure that the trailing zeroes are not the artifacts of formatting: they reflect the precision of the original data. The number of leading zeroes does not affect precision: the value 0.0067 is considered to have just 2 significant digits.
The seg
module includes a GiST
index operator class for seg
values.
The operators supported by the GiST operator class are shown in
Table F.28.
Table F.28. Seg GiST Operators
Operator  Description 

[a, b] << [c,
d] 
[a, b] is entirely to the left of [c, d]. That is, [a, b] << [c, d] is true if b < c and false otherwise. 
[a, b] >> [c,
d] 
[a, b] is entirely to the right of [c, d]. That is, [a, b] >> [c, d] is true if a > d and false otherwise. 
[a, b] &< [c,
d] 
Overlaps or is left of — This might be better read as “does not extend to right of”. It is true when b <= d. 
[a, b] &> [c,
d] 
Overlaps or is right of — This might be better read as “does not extend to left of”. It is true when a >= c. 
[a, b] = [c, d] 
Same as — The segments [a, b] and [c, d] are identical, that is, a = c and b = d. 
[a, b] && [c,
d] 
The segments [a, b] and [c, d] overlap. 
[a, b] @> [c,
d] 
The segment [a, b] contains the segment [c, d], that is, a <= c and b >= d. 
[a, b] <@ [c,
d] 
The segment [a, b] is contained in [c, d], that is, a >= c and b <= d. 
(Before PostgreSQL 8.2, the containment operators
@>
and <@
were respectively called @
and ~
. These
names are still available, but are deprecated and will
eventually be retired. Notice that the old names are reversed
from the convention formerly followed by the core geometric
data types!)
The standard Btree operators are also provided, for example
Operator  Description 

[a, b] < [c,
d] 
Less than 
[a, b] > [c,
d] 
Greater than 
These operators do not make a lot of sense for any practical purpose but sorting. These operators first compare (a) to (c), and if these are equal, compare (b) to (d). That results in reasonably good sorting in most cases, which is useful if you want to use ORDER BY with this type.
For examples of usage, see the regression test sql/seg.sql
.
The mechanism that converts (+)
to regular ranges isn't completely
accurate in determining the number of significant digits for
the boundaries. For example, it adds an extra digit to the
lower boundary if the resulting interval includes a power of
ten:
postgres=> select '10(+)1'::seg as seg; seg  9.0 .. 11  should be: 9 .. 11
The performance of an Rtree index can largely depend on the
initial order of input values. It may be very helpful to sort
the input table on the seg
column;
see the script sortsegments.pl
for an example.
Original author: Gene Selkov, Jr. <selkovjr@mcs.anl.gov>
,
Mathematics and Computer Science Division, Argonne National
Laboratory.
My thanks are primarily to Prof. Joe Hellerstein (http://db.cs.berkeley.edu/jmh/) for elucidating the gist of the GiST (http://gist.cs.berkeley.edu/). I am also grateful to all Postgres developers, present and past, for enabling myself to create my own world and live undisturbed in it. And I would like to acknowledge my gratitude to Argonne Lab and to the U.S. Department of Energy for the years of faithful support of my database research.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.