5 -- first, define the datatype. Turn off echoing so that expected file
6 -- does not depend on contents of seg.sql.
9 psql:seg.sql:10: NOTICE: type "seg" is not yet defined
10 DETAIL: Creating a shell type definition.
11 psql:seg.sql:15: NOTICE: argument type seg is only a shell
13 -- testing the input and output functions
16 SELECT '1'::seg AS seg;
22 SELECT '-1'::seg AS seg;
28 SELECT '1.0'::seg AS seg;
34 SELECT '-1.0'::seg AS seg;
40 SELECT '1e7'::seg AS seg;
46 SELECT '-1e7'::seg AS seg;
52 SELECT '1.0e7'::seg AS seg;
58 SELECT '-1.0e7'::seg AS seg;
64 SELECT '1e+7'::seg AS seg;
70 SELECT '-1e+7'::seg AS seg;
76 SELECT '1.0e+7'::seg AS seg;
82 SELECT '-1.0e+7'::seg AS seg;
88 SELECT '1e-7'::seg AS seg;
94 SELECT '-1e-7'::seg AS seg;
100 SELECT '1.0e-7'::seg AS seg;
106 SELECT '-1.0e-7'::seg AS seg;
112 SELECT '2e-6'::seg AS seg;
118 SELECT '2e-5'::seg AS seg;
124 SELECT '2e-4'::seg AS seg;
130 SELECT '2e-3'::seg AS seg;
136 SELECT '2e-2'::seg AS seg;
142 SELECT '2e-1'::seg AS seg;
148 SELECT '2e-0'::seg AS seg;
154 SELECT '2e+0'::seg AS seg;
160 SELECT '2e+1'::seg AS seg;
166 SELECT '2e+2'::seg AS seg;
172 SELECT '2e+3'::seg AS seg;
178 SELECT '2e+4'::seg AS seg;
184 SELECT '2e+5'::seg AS seg;
190 SELECT '2e+6'::seg AS seg;
196 -- Significant digits preserved
197 SELECT '1'::seg AS seg;
203 SELECT '1.0'::seg AS seg;
209 SELECT '1.00'::seg AS seg;
215 SELECT '1.000'::seg AS seg;
221 SELECT '1.0000'::seg AS seg;
227 SELECT '1.00000'::seg AS seg;
233 SELECT '1.000000'::seg AS seg;
239 SELECT '0.000000120'::seg AS seg;
245 SELECT '3.400e5'::seg AS seg;
252 SELECT '12.34567890123456'::seg AS seg;
258 -- Numbers with certainty indicators
259 SELECT '~6.5'::seg AS seg;
265 SELECT '<6.5'::seg AS seg;
271 SELECT '>6.5'::seg AS seg;
277 SELECT '~ 6.5'::seg AS seg;
283 SELECT '< 6.5'::seg AS seg;
289 SELECT '> 6.5'::seg AS seg;
296 SELECT '0..'::seg AS seg;
302 SELECT '0...'::seg AS seg;
308 SELECT '0 ..'::seg AS seg;
314 SELECT '0 ...'::seg AS seg;
320 SELECT '..0'::seg AS seg;
326 SELECT '...0'::seg AS seg;
332 SELECT '.. 0'::seg AS seg;
338 SELECT '... 0'::seg AS seg;
345 SELECT '0 .. 1'::seg AS seg;
351 SELECT '-1 .. 0'::seg AS seg;
357 SELECT '-1 .. 1'::seg AS seg;
364 SELECT '0(+-)1'::seg AS seg;
370 SELECT '0(+-)1.0'::seg AS seg;
376 SELECT '1.0(+-)0.005'::seg AS seg;
382 SELECT '101(+-)1'::seg AS seg;
388 -- incorrect number of significant digits in 99.0:
389 SELECT '100(+-)1'::seg AS seg;
396 SELECT ''::seg AS seg;
397 ERROR: bad seg representation
398 DETAIL: syntax error at end of input
399 SELECT 'ABC'::seg AS seg;
400 ERROR: bad seg representation
401 DETAIL: syntax error at or near "A"
402 SELECT '1ABC'::seg AS seg;
403 ERROR: bad seg representation
404 DETAIL: syntax error at or near "A"
405 SELECT '1.'::seg AS seg;
406 ERROR: bad seg representation
407 DETAIL: syntax error at or near "."
408 SELECT '1.....'::seg AS seg;
409 ERROR: bad seg representation
410 DETAIL: syntax error at or near ".."
411 SELECT '.1'::seg AS seg;
412 ERROR: bad seg representation
413 DETAIL: syntax error at or near "."
414 SELECT '1..2.'::seg AS seg;
415 ERROR: bad seg representation
416 DETAIL: syntax error at or near "."
417 SELECT '1 e7'::seg AS seg;
418 ERROR: bad seg representation
419 DETAIL: syntax error at or near "e"
420 SELECT '1e700'::seg AS seg;
422 DETAIL: numeric value 1e700 unrepresentable
424 -- testing the operators
426 -- equality/inequality:
428 SELECT '24 .. 33.20'::seg = '24 .. 33.20'::seg AS bool;
434 SELECT '24 .. 33.20'::seg = '24 .. 33.21'::seg AS bool;
440 SELECT '24 .. 33.20'::seg != '24 .. 33.20'::seg AS bool;
446 SELECT '24 .. 33.20'::seg != '24 .. 33.21'::seg AS bool;
454 SELECT '1'::seg && '1'::seg AS bool;
460 SELECT '1'::seg && '2'::seg AS bool;
466 SELECT '0 ..'::seg && '0 ..'::seg AS bool;
472 SELECT '0 .. 1'::seg && '0 .. 1'::seg AS bool;
478 SELECT '..0'::seg && '0..'::seg AS bool;
484 SELECT '-1 .. 0.1'::seg && '0 .. 1'::seg AS bool;
490 SELECT '-1 .. 0'::seg && '0 .. 1'::seg AS bool;
496 SELECT '-1 .. -0.0001'::seg && '0 .. 1'::seg AS bool;
502 SELECT '0 ..'::seg && '1'::seg AS bool;
508 SELECT '0 .. 1'::seg && '1'::seg AS bool;
514 SELECT '0 .. 1'::seg && '2'::seg AS bool;
520 SELECT '0 .. 2'::seg && '1'::seg AS bool;
526 SELECT '1'::seg && '0 .. 1'::seg AS bool;
532 SELECT '2'::seg && '0 .. 1'::seg AS bool;
538 SELECT '1'::seg && '0 .. 2'::seg AS bool;
544 -- overlap on the left
546 SELECT '1'::seg &< '0'::seg AS bool;
552 SELECT '1'::seg &< '1'::seg AS bool;
558 SELECT '1'::seg &< '2'::seg AS bool;
564 SELECT '0 .. 1'::seg &< '0'::seg AS bool;
570 SELECT '0 .. 1'::seg &< '1'::seg AS bool;
576 SELECT '0 .. 1'::seg &< '2'::seg AS bool;
582 SELECT '0 .. 1'::seg &< '0 .. 0.5'::seg AS bool;
588 SELECT '0 .. 1'::seg &< '0 .. 1'::seg AS bool;
594 SELECT '0 .. 1'::seg &< '0 .. 2'::seg AS bool;
600 SELECT '0 .. 1'::seg &< '1 .. 2'::seg AS bool;
606 SELECT '0 .. 1'::seg &< '2 .. 3'::seg AS bool;
612 -- overlap on the right
614 SELECT '0'::seg &> '1'::seg AS bool;
620 SELECT '1'::seg &> '1'::seg AS bool;
626 SELECT '2'::seg &> '1'::seg AS bool;
632 SELECT '0'::seg &> '0 .. 1'::seg AS bool;
638 SELECT '1'::seg &> '0 .. 1'::seg AS bool;
644 SELECT '2'::seg &> '0 .. 1'::seg AS bool;
650 SELECT '0 .. 0.5'::seg &> '0 .. 1'::seg AS bool;
656 SELECT '0 .. 1'::seg &> '0 .. 1'::seg AS bool;
662 SELECT '0 .. 2'::seg &> '0 .. 2'::seg AS bool;
668 SELECT '1 .. 2'::seg &> '0 .. 1'::seg AS bool;
674 SELECT '2 .. 3'::seg &> '0 .. 1'::seg AS bool;
682 SELECT '1'::seg << '0'::seg AS bool;
688 SELECT '1'::seg << '1'::seg AS bool;
694 SELECT '1'::seg << '2'::seg AS bool;
700 SELECT '0 .. 1'::seg << '0'::seg AS bool;
706 SELECT '0 .. 1'::seg << '1'::seg AS bool;
712 SELECT '0 .. 1'::seg << '2'::seg AS bool;
718 SELECT '0 .. 1'::seg << '0 .. 0.5'::seg AS bool;
724 SELECT '0 .. 1'::seg << '0 .. 1'::seg AS bool;
730 SELECT '0 .. 1'::seg << '0 .. 2'::seg AS bool;
736 SELECT '0 .. 1'::seg << '1 .. 2'::seg AS bool;
742 SELECT '0 .. 1'::seg << '2 .. 3'::seg AS bool;
750 SELECT '0'::seg >> '1'::seg AS bool;
756 SELECT '1'::seg >> '1'::seg AS bool;
762 SELECT '2'::seg >> '1'::seg AS bool;
768 SELECT '0'::seg >> '0 .. 1'::seg AS bool;
774 SELECT '1'::seg >> '0 .. 1'::seg AS bool;
780 SELECT '2'::seg >> '0 .. 1'::seg AS bool;
786 SELECT '0 .. 0.5'::seg >> '0 .. 1'::seg AS bool;
792 SELECT '0 .. 1'::seg >> '0 .. 1'::seg AS bool;
798 SELECT '0 .. 2'::seg >> '0 .. 2'::seg AS bool;
804 SELECT '1 .. 2'::seg >> '0 .. 1'::seg AS bool;
810 SELECT '2 .. 3'::seg >> '0 .. 1'::seg AS bool;
816 -- "contained in" (the left value belongs within the interval specified in the right value):
818 SELECT '0'::seg ~ '0'::seg AS bool;
824 SELECT '0'::seg ~ '0 ..'::seg AS bool;
830 SELECT '0'::seg ~ '.. 0'::seg AS bool;
836 SELECT '0'::seg ~ '-1 .. 1'::seg AS bool;
842 SELECT '0'::seg ~ '-1 .. 1'::seg AS bool;
848 SELECT '-1'::seg ~ '-1 .. 1'::seg AS bool;
854 SELECT '1'::seg ~ '-1 .. 1'::seg AS bool;
860 SELECT '-1 .. 1'::seg ~ '-1 .. 1'::seg AS bool;
866 -- "contains" (the left value contains the interval specified in the right value):
868 SELECT '0'::seg @ '0'::seg AS bool;
874 SELECT '0 .. '::seg ~ '0'::seg AS bool;
880 SELECT '.. 0'::seg ~ '0'::seg AS bool;
886 SELECT '-1 .. 1'::seg ~ '0'::seg AS bool;
892 SELECT '0'::seg ~ '-1 .. 1'::seg AS bool;
898 SELECT '-1'::seg ~ '-1 .. 1'::seg AS bool;
904 SELECT '1'::seg ~ '-1 .. 1'::seg AS bool;
910 -- Load some example data and build the index
912 CREATE TABLE test_seg (s seg);
913 \copy test_seg from 'data/test_seg.data'
914 CREATE INDEX test_seg_ix ON test_seg USING gist (s);
915 SELECT count(*) FROM test_seg WHERE s @ '11..11.3';
922 SELECT * FROM test_seg WHERE s @ '11..11.3' GROUP BY s;