6
-- testing the input and output functions
9
SELECT '1'::seg AS seg;
15
SELECT '-1'::seg AS seg;
21
SELECT '1.0'::seg AS seg;
27
SELECT '-1.0'::seg AS seg;
33
SELECT '1e7'::seg AS seg;
39
SELECT '-1e7'::seg AS seg;
45
SELECT '1.0e7'::seg AS seg;
51
SELECT '-1.0e7'::seg AS seg;
57
SELECT '1e+7'::seg AS seg;
63
SELECT '-1e+7'::seg AS seg;
69
SELECT '1.0e+7'::seg AS seg;
75
SELECT '-1.0e+7'::seg AS seg;
81
SELECT '1e-7'::seg AS seg;
87
SELECT '-1e-7'::seg AS seg;
93
SELECT '1.0e-7'::seg AS seg;
99
SELECT '-1.0e-7'::seg AS seg;
105
SELECT '2e-6'::seg AS seg;
111
SELECT '2e-5'::seg AS seg;
117
SELECT '2e-4'::seg AS seg;
123
SELECT '2e-3'::seg AS seg;
129
SELECT '2e-2'::seg AS seg;
135
SELECT '2e-1'::seg AS seg;
141
SELECT '2e-0'::seg AS seg;
147
SELECT '2e+0'::seg AS seg;
153
SELECT '2e+1'::seg AS seg;
159
SELECT '2e+2'::seg AS seg;
165
SELECT '2e+3'::seg AS seg;
171
SELECT '2e+4'::seg AS seg;
177
SELECT '2e+5'::seg AS seg;
183
SELECT '2e+6'::seg AS seg;
189
-- Significant digits preserved
190
SELECT '1'::seg AS seg;
196
SELECT '1.0'::seg AS seg;
202
SELECT '1.00'::seg AS seg;
208
SELECT '1.000'::seg AS seg;
214
SELECT '1.0000'::seg AS seg;
220
SELECT '1.00000'::seg AS seg;
226
SELECT '1.000000'::seg AS seg;
232
SELECT '0.000000120'::seg AS seg;
238
SELECT '3.400e5'::seg AS seg;
245
SELECT '12.34567890123456'::seg AS seg;
251
-- Numbers with certainty indicators
252
SELECT '~6.5'::seg AS seg;
258
SELECT '<6.5'::seg AS seg;
264
SELECT '>6.5'::seg AS seg;
270
SELECT '~ 6.5'::seg AS seg;
276
SELECT '< 6.5'::seg AS seg;
282
SELECT '> 6.5'::seg AS seg;
289
SELECT '0..'::seg AS seg;
295
SELECT '0...'::seg AS seg;
301
SELECT '0 ..'::seg AS seg;
307
SELECT '0 ...'::seg AS seg;
313
SELECT '..0'::seg AS seg;
319
SELECT '...0'::seg AS seg;
325
SELECT '.. 0'::seg AS seg;
331
SELECT '... 0'::seg AS seg;
338
SELECT '0 .. 1'::seg AS seg;
344
SELECT '-1 .. 0'::seg AS seg;
350
SELECT '-1 .. 1'::seg AS seg;
357
SELECT '0(+-)1'::seg AS seg;
363
SELECT '0(+-)1.0'::seg AS seg;
369
SELECT '1.0(+-)0.005'::seg AS seg;
375
SELECT '101(+-)1'::seg AS seg;
381
-- incorrect number of significant digits in 99.0:
382
SELECT '100(+-)1'::seg AS seg;
389
SELECT ''::seg AS seg;
390
ERROR: bad seg representation
391
LINE 1: SELECT ''::seg AS seg;
393
DETAIL: syntax error at end of input
394
SELECT 'ABC'::seg AS seg;
395
ERROR: bad seg representation
396
LINE 1: SELECT 'ABC'::seg AS seg;
398
DETAIL: syntax error at or near "A"
399
SELECT '1ABC'::seg AS seg;
400
ERROR: bad seg representation
401
LINE 1: SELECT '1ABC'::seg AS seg;
403
DETAIL: syntax error at or near "A"
404
SELECT '1.'::seg AS seg;
405
ERROR: bad seg representation
406
LINE 1: SELECT '1.'::seg AS seg;
408
DETAIL: syntax error at or near "."
409
SELECT '1.....'::seg AS seg;
410
ERROR: bad seg representation
411
LINE 1: SELECT '1.....'::seg AS seg;
413
DETAIL: syntax error at or near ".."
414
SELECT '.1'::seg AS seg;
415
ERROR: bad seg representation
416
LINE 1: SELECT '.1'::seg AS seg;
418
DETAIL: syntax error at or near "."
419
SELECT '1..2.'::seg AS seg;
420
ERROR: bad seg representation
421
LINE 1: SELECT '1..2.'::seg AS seg;
423
DETAIL: syntax error at or near "."
424
SELECT '1 e7'::seg AS seg;
425
ERROR: bad seg representation
426
LINE 1: SELECT '1 e7'::seg AS seg;
428
DETAIL: syntax error at or near "e"
429
SELECT '1e700'::seg AS seg;
430
ERROR: "1e700" is out of range for type real
431
LINE 1: SELECT '1e700'::seg AS seg;
434
-- testing the operators
436
-- equality/inequality:
438
SELECT '24 .. 33.20'::seg = '24 .. 33.20'::seg AS bool;
444
SELECT '24 .. 33.20'::seg = '24 .. 33.21'::seg AS bool;
450
SELECT '24 .. 33.20'::seg != '24 .. 33.20'::seg AS bool;
456
SELECT '24 .. 33.20'::seg != '24 .. 33.21'::seg AS bool;
464
SELECT '1'::seg && '1'::seg AS bool;
470
SELECT '1'::seg && '2'::seg AS bool;
476
SELECT '0 ..'::seg && '0 ..'::seg AS bool;
482
SELECT '0 .. 1'::seg && '0 .. 1'::seg AS bool;
488
SELECT '..0'::seg && '0..'::seg AS bool;
494
SELECT '-1 .. 0.1'::seg && '0 .. 1'::seg AS bool;
500
SELECT '-1 .. 0'::seg && '0 .. 1'::seg AS bool;
506
SELECT '-1 .. -0.0001'::seg && '0 .. 1'::seg AS bool;
512
SELECT '0 ..'::seg && '1'::seg AS bool;
518
SELECT '0 .. 1'::seg && '1'::seg AS bool;
524
SELECT '0 .. 1'::seg && '2'::seg AS bool;
530
SELECT '0 .. 2'::seg && '1'::seg AS bool;
536
SELECT '1'::seg && '0 .. 1'::seg AS bool;
542
SELECT '2'::seg && '0 .. 1'::seg AS bool;
548
SELECT '1'::seg && '0 .. 2'::seg AS bool;
554
-- overlap on the left
556
SELECT '1'::seg &< '0'::seg AS bool;
562
SELECT '1'::seg &< '1'::seg AS bool;
568
SELECT '1'::seg &< '2'::seg AS bool;
574
SELECT '0 .. 1'::seg &< '0'::seg AS bool;
580
SELECT '0 .. 1'::seg &< '1'::seg AS bool;
586
SELECT '0 .. 1'::seg &< '2'::seg AS bool;
592
SELECT '0 .. 1'::seg &< '0 .. 0.5'::seg AS bool;
598
SELECT '0 .. 1'::seg &< '0 .. 1'::seg AS bool;
604
SELECT '0 .. 1'::seg &< '0 .. 2'::seg AS bool;
610
SELECT '0 .. 1'::seg &< '1 .. 2'::seg AS bool;
616
SELECT '0 .. 1'::seg &< '2 .. 3'::seg AS bool;
622
-- overlap on the right
624
SELECT '0'::seg &> '1'::seg AS bool;
630
SELECT '1'::seg &> '1'::seg AS bool;
636
SELECT '2'::seg &> '1'::seg AS bool;
642
SELECT '0'::seg &> '0 .. 1'::seg AS bool;
648
SELECT '1'::seg &> '0 .. 1'::seg AS bool;
654
SELECT '2'::seg &> '0 .. 1'::seg AS bool;
660
SELECT '0 .. 0.5'::seg &> '0 .. 1'::seg AS bool;
666
SELECT '0 .. 1'::seg &> '0 .. 1'::seg AS bool;
672
SELECT '0 .. 2'::seg &> '0 .. 2'::seg AS bool;
678
SELECT '1 .. 2'::seg &> '0 .. 1'::seg AS bool;
684
SELECT '2 .. 3'::seg &> '0 .. 1'::seg AS bool;
692
SELECT '1'::seg << '0'::seg AS bool;
698
SELECT '1'::seg << '1'::seg AS bool;
704
SELECT '1'::seg << '2'::seg AS bool;
710
SELECT '0 .. 1'::seg << '0'::seg AS bool;
716
SELECT '0 .. 1'::seg << '1'::seg AS bool;
722
SELECT '0 .. 1'::seg << '2'::seg AS bool;
728
SELECT '0 .. 1'::seg << '0 .. 0.5'::seg AS bool;
734
SELECT '0 .. 1'::seg << '0 .. 1'::seg AS bool;
740
SELECT '0 .. 1'::seg << '0 .. 2'::seg AS bool;
746
SELECT '0 .. 1'::seg << '1 .. 2'::seg AS bool;
752
SELECT '0 .. 1'::seg << '2 .. 3'::seg AS bool;
760
SELECT '0'::seg >> '1'::seg AS bool;
766
SELECT '1'::seg >> '1'::seg AS bool;
772
SELECT '2'::seg >> '1'::seg AS bool;
778
SELECT '0'::seg >> '0 .. 1'::seg AS bool;
784
SELECT '1'::seg >> '0 .. 1'::seg AS bool;
790
SELECT '2'::seg >> '0 .. 1'::seg AS bool;
796
SELECT '0 .. 0.5'::seg >> '0 .. 1'::seg AS bool;
802
SELECT '0 .. 1'::seg >> '0 .. 1'::seg AS bool;
808
SELECT '0 .. 2'::seg >> '0 .. 2'::seg AS bool;
814
SELECT '1 .. 2'::seg >> '0 .. 1'::seg AS bool;
820
SELECT '2 .. 3'::seg >> '0 .. 1'::seg AS bool;
826
-- "contained in" (the left value belongs within the interval specified in the right value):
828
SELECT '0'::seg <@ '0'::seg AS bool;
834
SELECT '0'::seg <@ '0 ..'::seg AS bool;
840
SELECT '0'::seg <@ '.. 0'::seg AS bool;
846
SELECT '0'::seg <@ '-1 .. 1'::seg AS bool;
852
SELECT '0'::seg <@ '-1 .. 1'::seg AS bool;
858
SELECT '-1'::seg <@ '-1 .. 1'::seg AS bool;
864
SELECT '1'::seg <@ '-1 .. 1'::seg AS bool;
870
SELECT '-1 .. 1'::seg <@ '-1 .. 1'::seg AS bool;
876
-- "contains" (the left value contains the interval specified in the right value):
878
SELECT '0'::seg @> '0'::seg AS bool;
884
SELECT '0 .. '::seg <@ '0'::seg AS bool;
890
SELECT '.. 0'::seg <@ '0'::seg AS bool;
896
SELECT '-1 .. 1'::seg <@ '0'::seg AS bool;
902
SELECT '0'::seg <@ '-1 .. 1'::seg AS bool;
908
SELECT '-1'::seg <@ '-1 .. 1'::seg AS bool;
914
SELECT '1'::seg <@ '-1 .. 1'::seg AS bool;
920
-- Load some example data and build the index
922
CREATE TABLE test_seg (s seg);
923
\copy test_seg from 'data/test_seg.data'
924
CREATE INDEX test_seg_ix ON test_seg USING gist (s);
925
SELECT count(*) FROM test_seg WHERE s @> '11..11.3';
932
SELECT * FROM test_seg WHERE s @> '11..11.3' GROUP BY s;
1081
SELECT seg_lower(s), seg_center(s), seg_upper(s)
1082
FROM test_seg WHERE s @> '11.2..11.3' OR s IS NULL ORDER BY s;
1083
seg_lower | seg_center | seg_upper
1084
-----------+------------+-----------
1085
-Infinity | -Infinity | 40
1086
-Infinity | -Infinity | 82
1087
-Infinity | -Infinity | 90
1092
2.3 | Infinity | Infinity
1093
2.3 | Infinity | Infinity
1097
2.6 | Infinity | Infinity
1099
3 | Infinity | Infinity
1105
4 | Infinity | Infinity
1111
4 | Infinity | Infinity
1124
4.8 | Infinity | Infinity
1126
4.9 | Infinity | Infinity
1143
5.3 | Infinity | Infinity
1144
5.4 | Infinity | Infinity
1151
5.5 | Infinity | Infinity
1152
5.5 | Infinity | Infinity
1153
5.7 | Infinity | Infinity
1154
5.9 | Infinity | Infinity
1161
6.1 | Infinity | Infinity
1163
6.3 | Infinity | Infinity
1167
6.5 | Infinity | Infinity
1168
6.6 | Infinity | Infinity
1170
6.7 | Infinity | Infinity
1171
6.75 | Infinity | Infinity
1172
6.8 | Infinity | Infinity
1175
6.9 | Infinity | Infinity
1179
7 | Infinity | Infinity
1180
7.15 | Infinity | Infinity
1183
7.3 | Infinity | Infinity
1184
7.3 | Infinity | Infinity
1186
7.4 | Infinity | Infinity
1189
7.5 | Infinity | Infinity
1191
7.7 | Infinity | Infinity
1192
7.75 | Infinity | Infinity
1196
8.2 | Infinity | Infinity
1197
8.3 | Infinity | Infinity
1200
8.5 | Infinity | Infinity
1209
9 | Infinity | Infinity
1214
9.5 | Infinity | Infinity