The seg extension provides support for representing line segments or floating-point intervals in PostgreSQL. This type represents both ranges of values and also measurements with uncertainty, randomness or tolerances. Your Nile database arrives with the seg extension already enabled.

​
Overview

The seg extension adds a new data type seg that can represent:

  • Line segments on a number line
  • Floating-point intervals
  • Exact or inexact bounds
  • Infinite bounds using < x or > x notation

​
Data Type Format

The seg data type accepts several input formats:

FormatExampleDescription
x5.0Single value, a point
x .. y1.0 .. 2.0Interval from x to y
x ..1.0 ..Everything greater than x
.. y.. 2.0Everything less than y
<x<5.0A point at X. < is preserved as a comment
>x>5.0A point at X. > is preserved as a comment
~x~5.0A point at X. ~ is preserved as a comment
x(+-)d5.0(+-)0.1Interval from x-d to x+d

​
Examples

​
Creating Segments

-- Create basic intervals
SELECT '-1..1'::seg AS basic_interval;
SELECT '..5'::seg AS up_to_five;
SELECT '5..'::seg AS five_and_above;

-- Create approximate points
SELECT '~5.0'::seg AS approximately_five;

​
Comparison Operations

-- Check if segments overlap
SELECT '-1..1'::seg && '0..2'::seg AS overlaps;

-- Check if segment contains another
SELECT '-1..1'::seg @> '0..0.5'::seg AS contains;

-- Check if segment is contained by another
SELECT '0..0.5'::seg <@ '-1..1'::seg AS is_contained;

-- This returns false because the segment is not contained by the other
SELECT '-1..1'::seg @> '2..3'::seg AS contains;

​
Working with Measurement Ranges

CREATE TABLE measurements (
    id serial PRIMARY KEY,
    value seg
);

-- Insert measurements with tolerances
INSERT INTO measurements (value) VALUES 
    ('~10.5'),                -- Approximately 10.5
    ('10.3..10.7'),         -- Between 10.3 and 10.7
    ('..11.0'),               -- Less than 11.0
    ('9.5..');                -- Greater than 9.5

-- Find overlapping measurements
SELECT a.value, b.value 
FROM measurements a, measurements b 
WHERE a.value && b.value and a.value < b.value;

​
Use Cases

The seg extension can be used with any type of data that can be represented as a floating-point interval. But it is most useful for recording laboratory measurements with uncertainty or tolerances.

​
Performance Optimization

For better query performance with seg data:

  1. Create GiST indexes on seg columns:
-- Create a table with a seg column
CREATE TABLE temperature_readings (
    id serial PRIMARY KEY,
    location text,
    temp_range seg
);

-- Create a GiST index on the seg column
CREATE INDEX idx_temp_range ON temperature_readings USING gist(temp_range);
  1. Common operators that can use the GiST index:
  • << (strictly left of)
  • >> (strictly right of)
  • &< (does not extend right of)
  • &> (does not extend left of)
  • && (overlaps)
  • @> (contains)
  • <@ (contained in)

​
Additional Resources

Was this page helpful?

RDKitUnit