The topological operations available for the set and span types are given next.
A value, the span containing only that value, and the span set containing only that span denote the same set, and so do a span and the span set containing only it. Every operation gives the same answer for all of these spellings.
A span set is a union of disjoint spans. The operations &&, @>, <@ and ~= answer on the value itself, so a value that falls in a hole of a span set is not contained in it. The operation -|- and the position operations of the next section answer on the bounding span of the value, the smallest span containing it: the holes of a span set are not boundaries of it.
Do the values overlap (have values in common)?
{set,spans} && {set,spans} → boolean
SELECT intset '{1, 3}' && intset '{2, 3, 4}';
-- true
SELECT floatspan '[1, 3)' && floatspan '[3, 4)';
-- false
SELECT tstzspan '[2001-01-01, 2001-01-05)' && tstzspan '[2001-01-02, 2001-01-07)';
-- true
SELECT floatspanset '{[1, 5),[6, 8)}' && floatspan '[1, 6)';
-- true
Does the first value contain the second one?
{set,spans} @> {base,set,spans} → boolean
SELECT floatset '{1.5, 2.5}' @> 2.5;
-- true
SELECT tstzspan '[2001-01-01, 2001-05-01)' @> timestamptz '2001-02-01';
-- true
SELECT floatspanset '{[1, 2),(2, 3)}' @> 2.0;
-- false
Is the first value contained by the second one?
{base,set,spans} <@ {set,spans} → boolean
SELECT timestamptz '2001-01-10' <@ tstzspan '[2001-01-01, 2001-05-01)';
-- true
SELECT floatspan '[2, 5]' <@ floatspan '[1, 5)';
-- false
SELECT tstzspan '[2001-02-01, 2001-03-01)' <@ tstzspan '[2001-01-01, 2001-05-01)';
-- true
SELECT floatspanset '{[1,2],[3,4]}' <@ floatspan '[1, 6]';
-- true
Is the first value adjacent to the second one?
spans -|- spans → boolean
Two values are adjacent when they touch without overlapping, that is, when they share a boundary and have no value in common inside it. For the continuous base types float and timestamptz a shared bound is such a touch. For the discrete base types int, bigint and date, whose spans are stored in normal form, it means consecutive values.
SELECT intspan '[2, 6)' -|- intspan '[6, 7)';
-- true
SELECT intspan '[1, 5]' -|- intspan '[6, 10]';
-- true
SELECT intspan '[1, 5]' -|- intspan '[5, 10]';
-- false
SELECT floatspan '[2, 5)' -|- floatspan '(5, 6)';
-- true
SELECT floatspan '[1, 5]' -|- floatspan '[5, 9]';
-- true
SELECT floatspanset '{[2, 3],[4, 5)}' -|- floatspan '(5, 6)';
-- true
SELECT tstzspanset '{[2001-01-01, 2001-01-02]}' -|- tstzspan '[2001-01-02, 2001-01-03)';
-- true
A span set answers as its bounding span, and the three spellings of a value agree:
SELECT intspanset '{[1,3), [5,8), [10,12)}' -|- 3;
-- false
SELECT intspanset '{[1,3), [5,8), [10,12)}' -|- intspan '[3,3]';
-- false
SELECT intspanset '{[1,3), [5,8), [10,12)}' -|- intspanset '{[3,3]}';
-- false
SELECT intspanset '{[1,3), [5,8), [10,12)}' -|- 12;
-- true
The position operations available for set and span types are given next. Notice that the operators for time types have an additional # to distinguish them from the operators for number types.
Is the first value strictly left of the second one?
numbers << numbers → boolean times <<# times → boolean
SELECT intspan '[15, 20)' << 20;
-- true
SELECT intspanset '{[15, 17],[18, 20)}' << 20;
-- true
SELECT floatspan '[15, 20)' << floatspan '(15, 20)';
-- false
SELECT dateset '{2001-01-01, 2001-01-02}' <<# dateset '{2001-01-03, 2001-01-05}';
-- true
Is the first value strictly to the right of the second one?
numbers >> numbers → boolean times #>> times → boolean
SELECT intspan '[15, 20)' >> 10;
-- true
SELECT floatspan '[15, 20)' >> floatspan '[5, 10]';
-- true
SELECT floatspanset '{[15, 17], [18, 20)}' >> floatspan '[5, 10]';
-- true
SELECT tstzspan '[2001-01-04, 2001-01-05)' #>>
tstzspanset '{[2001-01-01, 2001-01-04), [2001-01-05, 2001-01-06)}';
-- true
Is the first value not to the right of the second one?
numbers &< numbers → boolean times &<# times → boolean
SELECT intspan '[15, 20)' &< 18;
-- false
SELECT intspanset '{[15, 16],[17, 18)}' &< 18;
-- true
SELECT floatspan '[15, 20)' &< floatspan '[10, 20]';
-- true
SELECT dateset '{2001-01-02, 2001-01-05}' &<# dateset '{2001-01-01, 2001-01-04}';
-- false
Is the first value not to the left of the second one?
numbers &> numbers → boolean times #&> times → boolean
SELECT intspan '[15, 20)' &> 30;
-- true
SELECT floatspan '[1, 6]' &> floatspan '(1, 3)';
-- false
SELECT floatspanset '{[1, 2],[3, 4]}' &> floatspan '(1, 3)';
-- false
SELECT timestamp '2001-01-01' #&> tstzspan '[2001-01-01, 2001-01-05)';
-- true
When creating indexes for set or span set types, what is stored in the index is not the actual value but instead, a bounding box that represents the value. In this case, the index will provide a list of candidate values that may satisfy the query predicate, and a second step is needed to filter out candidate values by computing the query predicate on the actual values.
However, when the bounding boxes have a large empty space not covered by the actual values, the index will generate many candidate values that do not satisfy the query predicate, which reduces the efficiency of the index. In these situations, it may be better to represent a value not with a single bounding box, but instead with multiple bounding boxes. This increases considerably the efficiency of the index, provided that the index is able to manage multiple bounding boxes per value. The following functions are used for generating multiple spans from a single set or span set value.
Return an array of N spans obtained by merging the elements of a set or the spans of a spanset
splitNSpans({set,spanset},integer) → span[]
The last argument specifies the number of output spans. If the number of input elements or spans is less than the given number, the resulting array will have one span per input element or span. Otherwise, the given number of output spans will be obtained by merging several consecutive input elements or spans.
SELECT splitNSpans(intset '{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}', 1);
-- {"[1, 11)"}
SELECT splitNSpans(intset '{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}', 3);
-- {"[1, 5)","[5, 8)","[8, 11)"}
SELECT splitNSpans(intset '{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}', 6);
-- {"[1, 3)","[3, 5)","[5, 7)","[7, 9)","[9, 10)","[10, 11)"}
SELECT splitNSpans(intset '{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}', 12);
/* {"[1, 2)","[2, 3)","[3, 4)","[4, 5)","[5, 6)","[6, 7)","[7, 8)","[8, 9)",
"[9, 10)","[10, 11)"} */
SELECT splitNSpans(intspanset '{[1, 2), [3, 4), [5, 6), [7, 8), [9, 10)}');
-- {"[1, 2)","[3, 4)","[5, 6)","[7, 8)","[9, 10)"}
SELECT splitNSpans(floatspanset '{[1, 2), [3, 4), [5, 6), [7, 8), [9, 10)}', 3);
-- {"[1, 4)","[5, 8)","[9, 10)"}
SELECT splitNSpans(datespanset '{[2001-01-01, 2001-01-04), [2001-01-05, 2001-01-10)}', 3);
-- {"[2001-01-01, 2001-01-04)","[2001-01-05, 2001-01-10)"}
Return an array of spans obtained by merging N consecutive elements of a set or N consecutive spans of a spanset
splitEachNSpans({set,spanset},integer) → span[]
The last argument specifies the number of input elements that are merged to produce an output span. If the number of input elements is less than the given number, the resulting array will have one output span per element. Otherwise, the given number of consecutive input elements will be merged into a single output span in the answer. Notice that, contrary to the splitNSpans function, the number of spans in the result depends on the number of input elements or spans.
SELECT splitEachNSpans(intset '{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}', 1);
/* {"[1, 2)","[2, 3)","[3, 4)","[4, 5)","[5, 6)","[6, 7)","[7, 8)","[8, 9)",
"[9, 10)","[10, 11)"} */
SELECT splitEachNSpans(intspanset '{[1, 2), [3, 4), [5, 6), [7, 8), [9, 10)}', 3);
-- {"[1, 6)","[7, 10)"}
SELECT splitEachNSpans(intset '{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}', 6);
-- {"[1, 7)","[7, 11)"}
SELECT splitEachNSpans(intset '{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}', 12);
-- {"[1, 11)"}