1300 lines
21 KiB
Plaintext
1300 lines
21 KiB
Plaintext
--
|
|
-- Test seg datatype
|
|
--
|
|
CREATE EXTENSION seg;
|
|
-- Check whether any of our opclasses fail amvalidate
|
|
SELECT amname, opcname
|
|
FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod
|
|
WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid);
|
|
amname | opcname
|
|
--------+---------
|
|
(0 rows)
|
|
|
|
--
|
|
-- testing the input and output functions
|
|
--
|
|
-- Any number
|
|
SELECT '1'::seg AS seg;
|
|
seg
|
|
-----
|
|
1
|
|
(1 row)
|
|
|
|
SELECT '-1'::seg AS seg;
|
|
seg
|
|
-----
|
|
-1
|
|
(1 row)
|
|
|
|
SELECT '1.0'::seg AS seg;
|
|
seg
|
|
-----
|
|
1.0
|
|
(1 row)
|
|
|
|
SELECT '-1.0'::seg AS seg;
|
|
seg
|
|
------
|
|
-1.0
|
|
(1 row)
|
|
|
|
SELECT '1e7'::seg AS seg;
|
|
seg
|
|
-------
|
|
1e+07
|
|
(1 row)
|
|
|
|
SELECT '-1e7'::seg AS seg;
|
|
seg
|
|
--------
|
|
-1e+07
|
|
(1 row)
|
|
|
|
SELECT '1.0e7'::seg AS seg;
|
|
seg
|
|
---------
|
|
1.0e+07
|
|
(1 row)
|
|
|
|
SELECT '-1.0e7'::seg AS seg;
|
|
seg
|
|
----------
|
|
-1.0e+07
|
|
(1 row)
|
|
|
|
SELECT '1e+7'::seg AS seg;
|
|
seg
|
|
-------
|
|
1e+07
|
|
(1 row)
|
|
|
|
SELECT '-1e+7'::seg AS seg;
|
|
seg
|
|
--------
|
|
-1e+07
|
|
(1 row)
|
|
|
|
SELECT '1.0e+7'::seg AS seg;
|
|
seg
|
|
---------
|
|
1.0e+07
|
|
(1 row)
|
|
|
|
SELECT '-1.0e+7'::seg AS seg;
|
|
seg
|
|
----------
|
|
-1.0e+07
|
|
(1 row)
|
|
|
|
SELECT '1e-7'::seg AS seg;
|
|
seg
|
|
-------
|
|
1e-07
|
|
(1 row)
|
|
|
|
SELECT '-1e-7'::seg AS seg;
|
|
seg
|
|
--------
|
|
-1e-07
|
|
(1 row)
|
|
|
|
SELECT '1.0e-7'::seg AS seg;
|
|
seg
|
|
---------
|
|
1.0e-07
|
|
(1 row)
|
|
|
|
SELECT '-1.0e-7'::seg AS seg;
|
|
seg
|
|
----------
|
|
-1.0e-07
|
|
(1 row)
|
|
|
|
SELECT '2e-6'::seg AS seg;
|
|
seg
|
|
-------
|
|
2e-06
|
|
(1 row)
|
|
|
|
SELECT '2e-5'::seg AS seg;
|
|
seg
|
|
-------
|
|
2e-05
|
|
(1 row)
|
|
|
|
SELECT '2e-4'::seg AS seg;
|
|
seg
|
|
--------
|
|
0.0002
|
|
(1 row)
|
|
|
|
SELECT '2e-3'::seg AS seg;
|
|
seg
|
|
-------
|
|
0.002
|
|
(1 row)
|
|
|
|
SELECT '2e-2'::seg AS seg;
|
|
seg
|
|
------
|
|
0.02
|
|
(1 row)
|
|
|
|
SELECT '2e-1'::seg AS seg;
|
|
seg
|
|
-----
|
|
0.2
|
|
(1 row)
|
|
|
|
SELECT '2e-0'::seg AS seg;
|
|
seg
|
|
-----
|
|
2
|
|
(1 row)
|
|
|
|
SELECT '2e+0'::seg AS seg;
|
|
seg
|
|
-----
|
|
2
|
|
(1 row)
|
|
|
|
SELECT '2e+1'::seg AS seg;
|
|
seg
|
|
-----
|
|
2e1
|
|
(1 row)
|
|
|
|
SELECT '2e+2'::seg AS seg;
|
|
seg
|
|
-----
|
|
2e2
|
|
(1 row)
|
|
|
|
SELECT '2e+3'::seg AS seg;
|
|
seg
|
|
-----
|
|
2e3
|
|
(1 row)
|
|
|
|
SELECT '2e+4'::seg AS seg;
|
|
seg
|
|
-----
|
|
2e4
|
|
(1 row)
|
|
|
|
SELECT '2e+5'::seg AS seg;
|
|
seg
|
|
-------
|
|
2e+05
|
|
(1 row)
|
|
|
|
SELECT '2e+6'::seg AS seg;
|
|
seg
|
|
-------
|
|
2e+06
|
|
(1 row)
|
|
|
|
-- Significant digits preserved
|
|
SELECT '1'::seg AS seg;
|
|
seg
|
|
-----
|
|
1
|
|
(1 row)
|
|
|
|
SELECT '1.0'::seg AS seg;
|
|
seg
|
|
-----
|
|
1.0
|
|
(1 row)
|
|
|
|
SELECT '1.00'::seg AS seg;
|
|
seg
|
|
------
|
|
1.00
|
|
(1 row)
|
|
|
|
SELECT '1.000'::seg AS seg;
|
|
seg
|
|
-------
|
|
1.000
|
|
(1 row)
|
|
|
|
SELECT '1.0000'::seg AS seg;
|
|
seg
|
|
--------
|
|
1.0000
|
|
(1 row)
|
|
|
|
SELECT '1.00000'::seg AS seg;
|
|
seg
|
|
---------
|
|
1.00000
|
|
(1 row)
|
|
|
|
SELECT '1.000000'::seg AS seg;
|
|
seg
|
|
---------
|
|
1.00000
|
|
(1 row)
|
|
|
|
SELECT '0.000000120'::seg AS seg;
|
|
seg
|
|
----------
|
|
1.20e-07
|
|
(1 row)
|
|
|
|
SELECT '3.400e5'::seg AS seg;
|
|
seg
|
|
-----------
|
|
3.400e+05
|
|
(1 row)
|
|
|
|
-- Digits truncated
|
|
SELECT '12.34567890123456'::seg AS seg;
|
|
seg
|
|
---------
|
|
12.3457
|
|
(1 row)
|
|
|
|
-- Same, with a very long input
|
|
SELECT '12.3456789012345600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'::seg AS seg;
|
|
seg
|
|
---------
|
|
12.3457
|
|
(1 row)
|
|
|
|
-- Numbers with certainty indicators
|
|
SELECT '~6.5'::seg AS seg;
|
|
seg
|
|
------
|
|
~6.5
|
|
(1 row)
|
|
|
|
SELECT '<6.5'::seg AS seg;
|
|
seg
|
|
------
|
|
<6.5
|
|
(1 row)
|
|
|
|
SELECT '>6.5'::seg AS seg;
|
|
seg
|
|
------
|
|
>6.5
|
|
(1 row)
|
|
|
|
SELECT '~ 6.5'::seg AS seg;
|
|
seg
|
|
------
|
|
~6.5
|
|
(1 row)
|
|
|
|
SELECT '< 6.5'::seg AS seg;
|
|
seg
|
|
------
|
|
<6.5
|
|
(1 row)
|
|
|
|
SELECT '> 6.5'::seg AS seg;
|
|
seg
|
|
------
|
|
>6.5
|
|
(1 row)
|
|
|
|
-- Open intervals
|
|
SELECT '0..'::seg AS seg;
|
|
seg
|
|
------
|
|
0 ..
|
|
(1 row)
|
|
|
|
SELECT '0...'::seg AS seg;
|
|
seg
|
|
------
|
|
0 ..
|
|
(1 row)
|
|
|
|
SELECT '0 ..'::seg AS seg;
|
|
seg
|
|
------
|
|
0 ..
|
|
(1 row)
|
|
|
|
SELECT '0 ...'::seg AS seg;
|
|
seg
|
|
------
|
|
0 ..
|
|
(1 row)
|
|
|
|
SELECT '..0'::seg AS seg;
|
|
seg
|
|
------
|
|
.. 0
|
|
(1 row)
|
|
|
|
SELECT '...0'::seg AS seg;
|
|
seg
|
|
------
|
|
.. 0
|
|
(1 row)
|
|
|
|
SELECT '.. 0'::seg AS seg;
|
|
seg
|
|
------
|
|
.. 0
|
|
(1 row)
|
|
|
|
SELECT '... 0'::seg AS seg;
|
|
seg
|
|
------
|
|
.. 0
|
|
(1 row)
|
|
|
|
-- Finite intervals
|
|
SELECT '0 .. 1'::seg AS seg;
|
|
seg
|
|
--------
|
|
0 .. 1
|
|
(1 row)
|
|
|
|
SELECT '-1 .. 0'::seg AS seg;
|
|
seg
|
|
---------
|
|
-1 .. 0
|
|
(1 row)
|
|
|
|
SELECT '-1 .. 1'::seg AS seg;
|
|
seg
|
|
---------
|
|
-1 .. 1
|
|
(1 row)
|
|
|
|
-- (+/-) intervals
|
|
SELECT '0(+-)1'::seg AS seg;
|
|
seg
|
|
---------
|
|
-1 .. 1
|
|
(1 row)
|
|
|
|
SELECT '0(+-)1.0'::seg AS seg;
|
|
seg
|
|
-------------
|
|
-1.0 .. 1.0
|
|
(1 row)
|
|
|
|
SELECT '1.0(+-)0.005'::seg AS seg;
|
|
seg
|
|
----------------
|
|
0.995 .. 1.005
|
|
(1 row)
|
|
|
|
SELECT '101(+-)1'::seg AS seg;
|
|
seg
|
|
------------------
|
|
1.00e2 .. 1.02e2
|
|
(1 row)
|
|
|
|
-- incorrect number of significant digits in 99.0:
|
|
SELECT '100(+-)1'::seg AS seg;
|
|
seg
|
|
----------------
|
|
99.0 .. 1.01e2
|
|
(1 row)
|
|
|
|
-- invalid input
|
|
SELECT ''::seg AS seg;
|
|
ERROR: bad seg representation
|
|
LINE 1: SELECT ''::seg AS seg;
|
|
^
|
|
DETAIL: syntax error at end of input
|
|
SELECT 'ABC'::seg AS seg;
|
|
ERROR: bad seg representation
|
|
LINE 1: SELECT 'ABC'::seg AS seg;
|
|
^
|
|
DETAIL: syntax error at or near "A"
|
|
SELECT '1ABC'::seg AS seg;
|
|
ERROR: bad seg representation
|
|
LINE 1: SELECT '1ABC'::seg AS seg;
|
|
^
|
|
DETAIL: syntax error at or near "A"
|
|
SELECT '1.'::seg AS seg;
|
|
ERROR: bad seg representation
|
|
LINE 1: SELECT '1.'::seg AS seg;
|
|
^
|
|
DETAIL: syntax error at or near "."
|
|
SELECT '1.....'::seg AS seg;
|
|
ERROR: bad seg representation
|
|
LINE 1: SELECT '1.....'::seg AS seg;
|
|
^
|
|
DETAIL: syntax error at or near ".."
|
|
SELECT '.1'::seg AS seg;
|
|
ERROR: bad seg representation
|
|
LINE 1: SELECT '.1'::seg AS seg;
|
|
^
|
|
DETAIL: syntax error at or near "."
|
|
SELECT '1..2.'::seg AS seg;
|
|
ERROR: bad seg representation
|
|
LINE 1: SELECT '1..2.'::seg AS seg;
|
|
^
|
|
DETAIL: syntax error at or near "."
|
|
SELECT '1 e7'::seg AS seg;
|
|
ERROR: bad seg representation
|
|
LINE 1: SELECT '1 e7'::seg AS seg;
|
|
^
|
|
DETAIL: syntax error at or near "e"
|
|
SELECT '1e700'::seg AS seg;
|
|
ERROR: "1e700" is out of range for type real
|
|
LINE 1: SELECT '1e700'::seg AS seg;
|
|
^
|
|
--
|
|
-- testing the operators
|
|
--
|
|
-- equality/inequality:
|
|
--
|
|
SELECT '24 .. 33.20'::seg = '24 .. 33.20'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '24 .. 33.20'::seg = '24 .. 33.21'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '24 .. 33.20'::seg != '24 .. 33.20'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '24 .. 33.20'::seg != '24 .. 33.21'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
-- overlap
|
|
--
|
|
SELECT '1'::seg && '1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '1'::seg && '2'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '0 ..'::seg && '0 ..'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg && '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '..0'::seg && '0..'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '-1 .. 0.1'::seg && '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '-1 .. 0'::seg && '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '-1 .. -0.0001'::seg && '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '0 ..'::seg && '1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg && '1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg && '2'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '0 .. 2'::seg && '1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '1'::seg && '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '2'::seg && '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '1'::seg && '0 .. 2'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
-- overlap on the left
|
|
--
|
|
SELECT '1'::seg &< '0'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '1'::seg &< '1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '1'::seg &< '2'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg &< '0'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg &< '1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg &< '2'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg &< '0 .. 0.5'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg &< '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg &< '0 .. 2'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg &< '1 .. 2'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg &< '2 .. 3'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
-- overlap on the right
|
|
--
|
|
SELECT '0'::seg &> '1'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '1'::seg &> '1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '2'::seg &> '1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0'::seg &> '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '1'::seg &> '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '2'::seg &> '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 0.5'::seg &> '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg &> '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 2'::seg &> '0 .. 2'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '1 .. 2'::seg &> '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '2 .. 3'::seg &> '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
-- left
|
|
--
|
|
SELECT '1'::seg << '0'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '1'::seg << '1'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '1'::seg << '2'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg << '0'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg << '1'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg << '2'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg << '0 .. 0.5'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg << '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg << '0 .. 2'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg << '1 .. 2'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg << '2 .. 3'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
-- right
|
|
--
|
|
SELECT '0'::seg >> '1'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '1'::seg >> '1'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '2'::seg >> '1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0'::seg >> '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '1'::seg >> '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '2'::seg >> '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. 0.5'::seg >> '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '0 .. 1'::seg >> '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '0 .. 2'::seg >> '0 .. 2'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '1 .. 2'::seg >> '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '2 .. 3'::seg >> '0 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
-- "contained in" (the left value belongs within the interval specified in the right value):
|
|
--
|
|
SELECT '0'::seg <@ '0'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0'::seg <@ '0 ..'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0'::seg <@ '.. 0'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0'::seg <@ '-1 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0'::seg <@ '-1 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '-1'::seg <@ '-1 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '1'::seg <@ '-1 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '-1 .. 1'::seg <@ '-1 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
-- "contains" (the left value contains the interval specified in the right value):
|
|
--
|
|
SELECT '0'::seg @> '0'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '0 .. '::seg <@ '0'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '.. 0'::seg <@ '0'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '-1 .. 1'::seg <@ '0'::seg AS bool;
|
|
bool
|
|
------
|
|
f
|
|
(1 row)
|
|
|
|
SELECT '0'::seg <@ '-1 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '-1'::seg <@ '-1 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT '1'::seg <@ '-1 .. 1'::seg AS bool;
|
|
bool
|
|
------
|
|
t
|
|
(1 row)
|
|
|
|
-- Load some example data and build the index
|
|
--
|
|
CREATE TABLE test_seg (s seg);
|
|
\copy test_seg from 'data/test_seg.data'
|
|
CREATE INDEX test_seg_ix ON test_seg USING gist (s);
|
|
SET enable_indexscan = false;
|
|
EXPLAIN (COSTS OFF)
|
|
SELECT count(*) FROM test_seg WHERE s @> '11..11.3';
|
|
QUERY PLAN
|
|
-------------------------------------------------------
|
|
Aggregate
|
|
-> Bitmap Heap Scan on test_seg
|
|
Recheck Cond: (s @> '1.1e1 .. 11.3'::seg)
|
|
-> Bitmap Index Scan on test_seg_ix
|
|
Index Cond: (s @> '1.1e1 .. 11.3'::seg)
|
|
(5 rows)
|
|
|
|
SELECT count(*) FROM test_seg WHERE s @> '11..11.3';
|
|
count
|
|
-------
|
|
143
|
|
(1 row)
|
|
|
|
RESET enable_indexscan;
|
|
SET enable_bitmapscan = false;
|
|
EXPLAIN (COSTS OFF)
|
|
SELECT count(*) FROM test_seg WHERE s @> '11..11.3';
|
|
QUERY PLAN
|
|
-----------------------------------------------------
|
|
Aggregate
|
|
-> Index Only Scan using test_seg_ix on test_seg
|
|
Index Cond: (s @> '1.1e1 .. 11.3'::seg)
|
|
(3 rows)
|
|
|
|
SELECT count(*) FROM test_seg WHERE s @> '11..11.3';
|
|
count
|
|
-------
|
|
143
|
|
(1 row)
|
|
|
|
RESET enable_bitmapscan;
|
|
-- Test sorting
|
|
SELECT * FROM test_seg WHERE s @> '11..11.3' GROUP BY s;
|
|
s
|
|
-----------------
|
|
.. 4.0e1
|
|
.. >8.2e1
|
|
.. 9.0e1
|
|
<1.0 .. >13.0
|
|
1.3 .. 12.0
|
|
2.0 .. 11.5
|
|
2.1 .. 11.8
|
|
<2.3 ..
|
|
>2.3 ..
|
|
2.4 .. 11.3
|
|
2.5 .. 11.5
|
|
2.5 .. 11.8
|
|
2.6 ..
|
|
2.7 .. 12.0
|
|
<3.0 ..
|
|
3 .. 5.8e1
|
|
3.1 .. 11.5
|
|
3.5 .. 11.5
|
|
3.5 .. 12.2
|
|
<4.0 .. >1.2e1
|
|
<4.0 ..
|
|
4 .. 1.2e1
|
|
4.0 .. 11.7
|
|
4.0 .. 12.5
|
|
4.0 .. 13.0
|
|
4.0 .. 6.0e1
|
|
4.0 ..
|
|
4.2 .. 11.5
|
|
4.2 .. 11.7
|
|
<4.5 .. >1.2e1
|
|
4.5 .. 11.5
|
|
4.5 .. <1.2e1
|
|
4.5 .. >1.2e1
|
|
4.5 .. 12.5
|
|
4.5 .. 1.15e2
|
|
4.7 .. 11.8
|
|
4.8 .. 11.5
|
|
4.8 .. 11.6
|
|
4.8 .. 12.5
|
|
4.8 ..
|
|
4.9 .. >1.2e1
|
|
4.9 ..
|
|
5 .. 11.5
|
|
5 .. 1.2e1
|
|
5 .. 3.0e1
|
|
5.0 .. 11.4
|
|
5.0 .. 11.5
|
|
5.0 .. 11.6
|
|
5.0 .. 11.7
|
|
5.0 .. 12.0
|
|
5.0 .. >12.0
|
|
5.0 .. >1.2e1
|
|
5.2 .. 11.5
|
|
5.2 .. >1.2e1
|
|
5.25 .. >1.2e1
|
|
5.3 .. 11.5
|
|
5.3 .. 1.3e1
|
|
5.3 .. >9.0e1
|
|
5.3 ..
|
|
5.4 ..
|
|
5.5 .. 11.5
|
|
5.5 .. 11.7
|
|
5.5 .. 1.2e1
|
|
5.5 .. >1.2e1
|
|
5.5 .. 12.5
|
|
5.5 .. 13.5
|
|
5.5 ..
|
|
>5.5 ..
|
|
5.7 ..
|
|
5.9 ..
|
|
6 .. 11.5
|
|
6 .. >1.2e1
|
|
6.0 .. 11.5
|
|
6.0 .. 1.3e1
|
|
>6.0 .. <11.5
|
|
6.1 .. >1.2e1
|
|
6.1 ..
|
|
6.2 .. >11.5
|
|
6.3 ..
|
|
6.5 .. 11.5
|
|
6.5 .. 12.0
|
|
6.5 .. >12.0
|
|
6.5 ..
|
|
6.6 ..
|
|
6.7 .. 11.5
|
|
6.7 ..
|
|
6.75 ..
|
|
6.8 ..
|
|
6.9 .. 12.2
|
|
6.9 .. >9.0e1
|
|
6.9 ..
|
|
<7.0 .. >11.5
|
|
7.0 .. 11.5
|
|
7.0 .. >11.5
|
|
7.0 ..
|
|
>7.15 ..
|
|
7.2 .. 13.5
|
|
7.3 .. >9.0e1
|
|
7.3 ..
|
|
>7.3 ..
|
|
7.4 .. 12.1
|
|
7.4 ..
|
|
7.5 .. 11.5
|
|
7.5 .. 12.0
|
|
7.5 ..
|
|
7.7 .. 11.5
|
|
7.7 ..
|
|
7.75 ..
|
|
8.0 .. 11.7
|
|
8.0 .. 12.0
|
|
8.0 .. >13.0
|
|
8.2 ..
|
|
8.3 ..
|
|
8.5 .. >11.5
|
|
8.5 .. 12.5
|
|
8.5 ..
|
|
8.6 .. >9.9e1
|
|
8.7 .. 11.3
|
|
8.7 .. 11.7
|
|
8.9 .. 11.5
|
|
9 .. >1.2e1
|
|
9.0 .. 11.3
|
|
9.0 .. 11.5
|
|
9.0 .. 1.2e1
|
|
9.0 ..
|
|
9.2 .. 1.2e1
|
|
9.4 .. 12.2
|
|
<9.5 .. 1.2e1
|
|
<9.5 .. >12.2
|
|
9.5 ..
|
|
9.6 .. 11.5
|
|
9.7 .. 11.5
|
|
9.7 .. >1.2e1
|
|
9.8 .. >12.5
|
|
<1.0e1 .. >11.6
|
|
10.0 .. 11.5
|
|
10.0 .. 12.5
|
|
10.0 .. >12.5
|
|
10.2 .. 11.8
|
|
<10.5 .. 11.5
|
|
10.5 .. 11.5
|
|
10.5 .. <13.5
|
|
10.7 .. 12.3
|
|
(143 rows)
|
|
|
|
-- Test functions
|
|
SELECT seg_lower(s), seg_center(s), seg_upper(s)
|
|
FROM test_seg WHERE s @> '11.2..11.3' OR s IS NULL ORDER BY s;
|
|
seg_lower | seg_center | seg_upper
|
|
-----------+------------+-----------
|
|
-Infinity | -Infinity | 40
|
|
-Infinity | -Infinity | 82
|
|
-Infinity | -Infinity | 90
|
|
1 | 7 | 13
|
|
1.3 | 6.65 | 12
|
|
2 | 6.75 | 11.5
|
|
2.1 | 6.95 | 11.8
|
|
2.3 | Infinity | Infinity
|
|
2.3 | Infinity | Infinity
|
|
2.4 | 6.8500004 | 11.3
|
|
2.5 | 7 | 11.5
|
|
2.5 | 7.15 | 11.8
|
|
2.6 | Infinity | Infinity
|
|
2.7 | 7.35 | 12
|
|
3 | Infinity | Infinity
|
|
3 | 30.5 | 58
|
|
3.1 | 7.3 | 11.5
|
|
3.5 | 7.5 | 11.5
|
|
3.5 | 7.85 | 12.2
|
|
4 | 8 | 12
|
|
4 | Infinity | Infinity
|
|
4 | 8 | 12
|
|
4 | 7.85 | 11.7
|
|
4 | 8.25 | 12.5
|
|
4 | 8.5 | 13
|
|
4 | 32 | 60
|
|
4 | Infinity | Infinity
|
|
4.2 | 7.85 | 11.5
|
|
4.2 | 7.95 | 11.7
|
|
4.5 | 8.25 | 12
|
|
4.5 | 8 | 11.5
|
|
4.5 | 8.25 | 12
|
|
4.5 | 8.25 | 12
|
|
4.5 | 8.5 | 12.5
|
|
4.5 | 59.75 | 115
|
|
4.7 | 8.25 | 11.8
|
|
4.8 | 8.15 | 11.5
|
|
4.8 | 8.200001 | 11.6
|
|
4.8 | 8.65 | 12.5
|
|
4.8 | Infinity | Infinity
|
|
4.9 | 8.45 | 12
|
|
4.9 | Infinity | Infinity
|
|
5 | 8.25 | 11.5
|
|
5 | 8.5 | 12
|
|
5 | 17.5 | 30
|
|
5 | 8.2 | 11.4
|
|
5 | 8.25 | 11.5
|
|
5 | 8.3 | 11.6
|
|
5 | 8.35 | 11.7
|
|
5 | 8.5 | 12
|
|
5 | 8.5 | 12
|
|
5 | 8.5 | 12
|
|
5.2 | 8.35 | 11.5
|
|
5.2 | 8.6 | 12
|
|
5.25 | 8.625 | 12
|
|
5.3 | 8.4 | 11.5
|
|
5.3 | 9.15 | 13
|
|
5.3 | 47.65 | 90
|
|
5.3 | Infinity | Infinity
|
|
5.4 | Infinity | Infinity
|
|
5.5 | 8.5 | 11.5
|
|
5.5 | 8.6 | 11.7
|
|
5.5 | 8.75 | 12
|
|
5.5 | 8.75 | 12
|
|
5.5 | 9 | 12.5
|
|
5.5 | 9.5 | 13.5
|
|
5.5 | Infinity | Infinity
|
|
5.5 | Infinity | Infinity
|
|
5.7 | Infinity | Infinity
|
|
5.9 | Infinity | Infinity
|
|
6 | 8.75 | 11.5
|
|
6 | 9 | 12
|
|
6 | 8.75 | 11.5
|
|
6 | 9.5 | 13
|
|
6 | 8.75 | 11.5
|
|
6.1 | 9.05 | 12
|
|
6.1 | Infinity | Infinity
|
|
6.2 | 8.85 | 11.5
|
|
6.3 | Infinity | Infinity
|
|
6.5 | 9 | 11.5
|
|
6.5 | 9.25 | 12
|
|
6.5 | 9.25 | 12
|
|
6.5 | Infinity | Infinity
|
|
6.6 | Infinity | Infinity
|
|
6.7 | 9.1 | 11.5
|
|
6.7 | Infinity | Infinity
|
|
6.75 | Infinity | Infinity
|
|
6.8 | Infinity | Infinity
|
|
6.9 | 9.55 | 12.2
|
|
6.9 | 48.45 | 90
|
|
6.9 | Infinity | Infinity
|
|
7 | 9.25 | 11.5
|
|
7 | 9.25 | 11.5
|
|
7 | 9.25 | 11.5
|
|
7 | Infinity | Infinity
|
|
7.15 | Infinity | Infinity
|
|
7.2 | 10.35 | 13.5
|
|
7.3 | 48.65 | 90
|
|
7.3 | Infinity | Infinity
|
|
7.3 | Infinity | Infinity
|
|
7.4 | 9.75 | 12.1
|
|
7.4 | Infinity | Infinity
|
|
7.5 | 9.5 | 11.5
|
|
7.5 | 9.75 | 12
|
|
7.5 | Infinity | Infinity
|
|
7.7 | 9.6 | 11.5
|
|
7.7 | Infinity | Infinity
|
|
7.75 | Infinity | Infinity
|
|
8 | 9.85 | 11.7
|
|
8 | 10 | 12
|
|
8 | 10.5 | 13
|
|
8.2 | Infinity | Infinity
|
|
8.3 | Infinity | Infinity
|
|
8.5 | 10 | 11.5
|
|
8.5 | 10.5 | 12.5
|
|
8.5 | Infinity | Infinity
|
|
8.6 | 53.8 | 99
|
|
8.7 | 10 | 11.3
|
|
8.7 | 10.2 | 11.7
|
|
8.9 | 10.2 | 11.5
|
|
9 | 10.5 | 12
|
|
9 | 10.15 | 11.3
|
|
9 | 10.25 | 11.5
|
|
9 | 10.5 | 12
|
|
9 | Infinity | Infinity
|
|
9.2 | 10.6 | 12
|
|
9.4 | 10.799999 | 12.2
|
|
9.5 | 10.75 | 12
|
|
9.5 | 10.85 | 12.2
|
|
9.5 | Infinity | Infinity
|
|
9.6 | 10.55 | 11.5
|
|
9.7 | 10.6 | 11.5
|
|
9.7 | 10.85 | 12
|
|
9.8 | 11.15 | 12.5
|
|
10 | 10.8 | 11.6
|
|
10 | 10.75 | 11.5
|
|
10 | 11.25 | 12.5
|
|
10 | 11.25 | 12.5
|
|
10.2 | 11 | 11.8
|
|
10.5 | 11 | 11.5
|
|
10.5 | 11 | 11.5
|
|
10.5 | 12 | 13.5
|
|
10.7 | 11.5 | 12.3
|
|
| |
|
|
(144 rows)
|
|
|
|
-- test non error throwing API
|
|
SELECT str as seg,
|
|
pg_input_is_valid(str,'seg') as ok,
|
|
errinfo.sql_error_code,
|
|
errinfo.message,
|
|
errinfo.detail,
|
|
errinfo.hint
|
|
FROM unnest(ARRAY['-1 .. 1'::text,
|
|
'100(+-)1',
|
|
'',
|
|
'ABC',
|
|
'1 e7',
|
|
'1e700']) str,
|
|
LATERAL pg_input_error_info(str, 'seg') as errinfo;
|
|
seg | ok | sql_error_code | message | detail | hint
|
|
----------+----+----------------+---------------------------------------+------------------------------+------
|
|
-1 .. 1 | t | | | |
|
|
100(+-)1 | t | | | |
|
|
| f | 42601 | bad seg representation | syntax error at end of input |
|
|
ABC | f | 42601 | bad seg representation | syntax error at or near "A" |
|
|
1 e7 | f | 42601 | bad seg representation | syntax error at or near "e" |
|
|
1e700 | f | 22003 | "1e700" is out of range for type real | |
|
|
(6 rows)
|
|
|