PostgreSQL provides an extension called seg that allows you to work with one-dimensional numeric ranges (segments). This data type is useful when dealing with intervals such as numeric ranges, measurements, or boundaries where operations like overlap, containment, and comparison are required.
Enabling the seg Extension
Before using the seg data type, you need to enable the extension:
CREATE EXTENSION seg;
You can verify the available functions and operators using:
\dx+ seg
This will list all functions, operators, operator classes, and types related to the seg extension.
Result :
postgres=# \dx+ seg
Objects in extension "seg"
Object description
--------------------------------------------------------------
function gseg_consistent(internal,seg,smallint,oid,internal)
function gseg_penalty(internal,internal,internal)
function gseg_picksplit(internal,internal)
function gseg_same(seg,seg,internal)
function gseg_union(internal,internal)
function seg_center(seg)
function seg_cmp(seg,seg)
function seg_contained(seg,seg)
function seg_contains(seg,seg)
function seg_different(seg,seg)
function seg_ge(seg,seg)
function seg_gt(seg,seg)
function seg_in(cstring)
function seg_inter(seg,seg)
function seg_left(seg,seg)
function seg_le(seg,seg)
function seg_lower(seg)
function seg_lt(seg,seg)
function seg_out(seg)
function seg_overlap(seg,seg)
function seg_over_left(seg,seg)
function seg_over_right(seg,seg)
function seg_right(seg,seg)
function seg_same(seg,seg)
function seg_size(seg)
function seg_union(seg,seg)
function seg_upper(seg)
Creating a Table with seg datatype
CREATE TABLE seg_test (
id SERIAL PRIMARY KEY,
r seg
);
Inserting Data
INSERT INTO seg_test (r) VALUES
('1..5'),
('3..7'),
('6..10'),
('8..12');
Viewing Data
SELECT * FROM seg_test;
Result:
id | r
----+------------
1 | 1 .. 5
2 | 3 .. 7
3 | 6 .. 1.0e1
4 | 8 .. 1.2e1
Access Functions
Lower and Upper Bounds
SELECT id, seg_lower(r), seg_upper(r) FROM seg_test;
Result:
id | seg_lower | seg_upper
----+-----------+-----------
1 | 1 | 5
2 | 3 | 7
3 | 6 | 10
4 | 8 | 12
Purpose:
- seg_lower returns the starting value
- seg_upper returns the ending value
Center of Segment
SELECT id, seg_center(r) FROM seg_test;
Result:
id | seg_center
----+------------
1 | 3
2 | 5
3 | 8
4 | 10
Purpose:
- Returns the midpoint of the segment
Segment Size
SELECT id, seg_size(r) FROM seg_test;
Result:
id | seg_size
----+----------
1 | 4
2 | 4
3 | 4
4 | 4
Purpose:
- Calculates the length of the segment (upper - lower)
Comparison Functions
Seg_cmp
SELECT seg_cmp('1..5', '3..7');Result:
seg_cmp
---------
-1
More examples:
SELECT seg_cmp('1..5', '3..9'); -- -1
SELECT seg_cmp('1..15', '1..15'); -- 0
SELECT seg_cmp('5..10', '3..9'); -- 1Purpose:
- Returns:
- -1 if first < second
- 0 if equal
- 1 if first > second
Comparison is based on the lower bound first, then the upper bound.
Boolean Comparison
SELECT seg_lt('1..5', '3..7');Result:
t
More examples:
SELECT seg_lt('1..6', '3..7'); -- true
SELECT seg_lt('6..12', '3..7'); -- falsePurpose:
- seg_lt, seg_gt, seg_le, seg_ge provide boolean comparisons
Relationship Functions
Overlap
SELECT * FROM seg_test
WHERE seg_overlap(r, '4..8');
Result:
id | r
----+------------
1 | 1 .. 5
2 | 3 .. 7
3 | 6 .. 1.0e1
4 | 8 .. 1.2e1
Purpose:
- Checks if two segments overlap
Contains
SELECT * FROM seg_test
WHERE seg_contains(r, '4..6');
Result:
id | r
----+--------
2 | 3 .. 7
More examples:
SELECT * FROM seg_test WHERE seg_contains(r, '5..10'); -- no rows
SELECT * FROM seg_test WHERE seg_contains(r, '1..5'); -- id 1
Purpose:
- Checks if one segment fully contains another
Contained Within
SELECT * FROM seg_test
WHERE seg_contained(r, '1..10');
Result:
id | r
----+------------
1 | 1 .. 5
2 | 3 .. 7
3 | 6 .. 1.0e1
More examples:
SELECT * FROM seg_test WHERE seg_contained(r, '1..5');
SELECT * FROM seg_test WHERE seg_contained(r, '1..6');
SELECT * FROM seg_test WHERE seg_contained(r, '1..9');
Purpose:
- Checks if a segment lies inside another
Left and Right
SELECT * FROM seg_test
WHERE seg_left(r, '6..8');
Result:
id | r
----+--------
1 | 1 .. 5
SELECT * FROM seg_test
WHERE seg_right(r, '2..3');
Result:
id | r
----+------------
3 | 6 .. 1.0e1
4 | 8 .. 1.2e1
Purpose:
- seg_left: completely to the left
- seg_right: completely to the right
Over Left and Over Right
SELECT seg_over_left('1..5', '3..7');Result:
t
More examples
SELECT seg_over_left('3..7', '3..7'); -- true
SELECT seg_over_left('3..7', '1..5'); -- false
SELECT seg_over_right('3..7', '1..5');Purpose:
- Checks partial positioning with overlap
Equality and Difference
SELECT seg_same('1..5', '1..5');Result:
t
More examples
SELECT seg_same('1..5', '1..6'); -- false
SELECT seg_different('1..5', '3..7'); -- true
SELECT seg_different('1..5', '1..5'); -- falsePurpose:
- seg_same: checks equality
- seg_different: checks inequality
Set Operations
Union
SELECT seg_union('1..5', '3..7');Result:
1 .. 7
Purpose:
- Combines two segments into one covering both ranges
Intersection
SELECT seg_inter('1..5', '3..7');Result:
3 .. 5
Purpose:
- Returns overlapping portion of two segments
Join Example (Advanced Use Case)
SELECT a.id, b.id
FROM seg_test a
JOIN seg_test b
ON seg_overlap(a.r, b.r)
WHERE a.id < b.id;
Result:
id | id
----+----
1 | 2
2 | 3
3 | 4
Purpose:
- Finds overlapping segments across rows
Input and Output Functions
seg_in
SELECT seg_in('2..6');Result:
2 .. 6
Seg_out
SELECT seg_out('2..6'::seg);Result:
2 .. 6
Purpose:
- seg_in: converts string to seg
- seg_out: converts seg to string
The seg data type in PostgreSQL is designed for handling numeric intervals efficiently. It provides a rich set of functions and operators for:
- Accessing segment properties
- Comparing segments
- Checking relationships like overlap and containment
- Performing set operations such as union and intersection
Although modern PostgreSQL provides built-in range types, the seg extension remains useful for understanding range operations and exploring index-based optimizations, especially with GiST indexes.