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?