1-- 2-- Test seg datatype 3-- 4 5CREATE EXTENSION seg; 6 7-- Check whether any of our opclasses fail amvalidate 8SELECT amname, opcname 9FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod 10WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid); 11 12-- 13-- testing the input and output functions 14-- 15 16-- Any number 17SELECT '1'::seg AS seg; 18SELECT '-1'::seg AS seg; 19SELECT '1.0'::seg AS seg; 20SELECT '-1.0'::seg AS seg; 21SELECT '1e7'::seg AS seg; 22SELECT '-1e7'::seg AS seg; 23SELECT '1.0e7'::seg AS seg; 24SELECT '-1.0e7'::seg AS seg; 25SELECT '1e+7'::seg AS seg; 26SELECT '-1e+7'::seg AS seg; 27SELECT '1.0e+7'::seg AS seg; 28SELECT '-1.0e+7'::seg AS seg; 29SELECT '1e-7'::seg AS seg; 30SELECT '-1e-7'::seg AS seg; 31SELECT '1.0e-7'::seg AS seg; 32SELECT '-1.0e-7'::seg AS seg; 33SELECT '2e-6'::seg AS seg; 34SELECT '2e-5'::seg AS seg; 35SELECT '2e-4'::seg AS seg; 36SELECT '2e-3'::seg AS seg; 37SELECT '2e-2'::seg AS seg; 38SELECT '2e-1'::seg AS seg; 39SELECT '2e-0'::seg AS seg; 40SELECT '2e+0'::seg AS seg; 41SELECT '2e+1'::seg AS seg; 42SELECT '2e+2'::seg AS seg; 43SELECT '2e+3'::seg AS seg; 44SELECT '2e+4'::seg AS seg; 45SELECT '2e+5'::seg AS seg; 46SELECT '2e+6'::seg AS seg; 47 48 49-- Significant digits preserved 50SELECT '1'::seg AS seg; 51SELECT '1.0'::seg AS seg; 52SELECT '1.00'::seg AS seg; 53SELECT '1.000'::seg AS seg; 54SELECT '1.0000'::seg AS seg; 55SELECT '1.00000'::seg AS seg; 56SELECT '1.000000'::seg AS seg; 57SELECT '0.000000120'::seg AS seg; 58SELECT '3.400e5'::seg AS seg; 59 60-- Digits truncated 61SELECT '12.34567890123456'::seg AS seg; 62 63-- Numbers with certainty indicators 64SELECT '~6.5'::seg AS seg; 65SELECT '<6.5'::seg AS seg; 66SELECT '>6.5'::seg AS seg; 67SELECT '~ 6.5'::seg AS seg; 68SELECT '< 6.5'::seg AS seg; 69SELECT '> 6.5'::seg AS seg; 70 71-- Open intervals 72SELECT '0..'::seg AS seg; 73SELECT '0...'::seg AS seg; 74SELECT '0 ..'::seg AS seg; 75SELECT '0 ...'::seg AS seg; 76SELECT '..0'::seg AS seg; 77SELECT '...0'::seg AS seg; 78SELECT '.. 0'::seg AS seg; 79SELECT '... 0'::seg AS seg; 80 81-- Finite intervals 82SELECT '0 .. 1'::seg AS seg; 83SELECT '-1 .. 0'::seg AS seg; 84SELECT '-1 .. 1'::seg AS seg; 85 86-- (+/-) intervals 87SELECT '0(+-)1'::seg AS seg; 88SELECT '0(+-)1.0'::seg AS seg; 89SELECT '1.0(+-)0.005'::seg AS seg; 90SELECT '101(+-)1'::seg AS seg; 91-- incorrect number of significant digits in 99.0: 92SELECT '100(+-)1'::seg AS seg; 93 94-- invalid input 95SELECT ''::seg AS seg; 96SELECT 'ABC'::seg AS seg; 97SELECT '1ABC'::seg AS seg; 98SELECT '1.'::seg AS seg; 99SELECT '1.....'::seg AS seg; 100SELECT '.1'::seg AS seg; 101SELECT '1..2.'::seg AS seg; 102SELECT '1 e7'::seg AS seg; 103SELECT '1e700'::seg AS seg; 104 105-- 106-- testing the operators 107-- 108 109-- equality/inequality: 110-- 111SELECT '24 .. 33.20'::seg = '24 .. 33.20'::seg AS bool; 112SELECT '24 .. 33.20'::seg = '24 .. 33.21'::seg AS bool; 113SELECT '24 .. 33.20'::seg != '24 .. 33.20'::seg AS bool; 114SELECT '24 .. 33.20'::seg != '24 .. 33.21'::seg AS bool; 115 116-- overlap 117-- 118SELECT '1'::seg && '1'::seg AS bool; 119SELECT '1'::seg && '2'::seg AS bool; 120SELECT '0 ..'::seg && '0 ..'::seg AS bool; 121SELECT '0 .. 1'::seg && '0 .. 1'::seg AS bool; 122SELECT '..0'::seg && '0..'::seg AS bool; 123SELECT '-1 .. 0.1'::seg && '0 .. 1'::seg AS bool; 124SELECT '-1 .. 0'::seg && '0 .. 1'::seg AS bool; 125SELECT '-1 .. -0.0001'::seg && '0 .. 1'::seg AS bool; 126SELECT '0 ..'::seg && '1'::seg AS bool; 127SELECT '0 .. 1'::seg && '1'::seg AS bool; 128SELECT '0 .. 1'::seg && '2'::seg AS bool; 129SELECT '0 .. 2'::seg && '1'::seg AS bool; 130SELECT '1'::seg && '0 .. 1'::seg AS bool; 131SELECT '2'::seg && '0 .. 1'::seg AS bool; 132SELECT '1'::seg && '0 .. 2'::seg AS bool; 133 134-- overlap on the left 135-- 136SELECT '1'::seg &< '0'::seg AS bool; 137SELECT '1'::seg &< '1'::seg AS bool; 138SELECT '1'::seg &< '2'::seg AS bool; 139SELECT '0 .. 1'::seg &< '0'::seg AS bool; 140SELECT '0 .. 1'::seg &< '1'::seg AS bool; 141SELECT '0 .. 1'::seg &< '2'::seg AS bool; 142SELECT '0 .. 1'::seg &< '0 .. 0.5'::seg AS bool; 143SELECT '0 .. 1'::seg &< '0 .. 1'::seg AS bool; 144SELECT '0 .. 1'::seg &< '0 .. 2'::seg AS bool; 145SELECT '0 .. 1'::seg &< '1 .. 2'::seg AS bool; 146SELECT '0 .. 1'::seg &< '2 .. 3'::seg AS bool; 147 148-- overlap on the right 149-- 150SELECT '0'::seg &> '1'::seg AS bool; 151SELECT '1'::seg &> '1'::seg AS bool; 152SELECT '2'::seg &> '1'::seg AS bool; 153SELECT '0'::seg &> '0 .. 1'::seg AS bool; 154SELECT '1'::seg &> '0 .. 1'::seg AS bool; 155SELECT '2'::seg &> '0 .. 1'::seg AS bool; 156SELECT '0 .. 0.5'::seg &> '0 .. 1'::seg AS bool; 157SELECT '0 .. 1'::seg &> '0 .. 1'::seg AS bool; 158SELECT '0 .. 2'::seg &> '0 .. 2'::seg AS bool; 159SELECT '1 .. 2'::seg &> '0 .. 1'::seg AS bool; 160SELECT '2 .. 3'::seg &> '0 .. 1'::seg AS bool; 161 162-- left 163-- 164SELECT '1'::seg << '0'::seg AS bool; 165SELECT '1'::seg << '1'::seg AS bool; 166SELECT '1'::seg << '2'::seg AS bool; 167SELECT '0 .. 1'::seg << '0'::seg AS bool; 168SELECT '0 .. 1'::seg << '1'::seg AS bool; 169SELECT '0 .. 1'::seg << '2'::seg AS bool; 170SELECT '0 .. 1'::seg << '0 .. 0.5'::seg AS bool; 171SELECT '0 .. 1'::seg << '0 .. 1'::seg AS bool; 172SELECT '0 .. 1'::seg << '0 .. 2'::seg AS bool; 173SELECT '0 .. 1'::seg << '1 .. 2'::seg AS bool; 174SELECT '0 .. 1'::seg << '2 .. 3'::seg AS bool; 175 176-- right 177-- 178SELECT '0'::seg >> '1'::seg AS bool; 179SELECT '1'::seg >> '1'::seg AS bool; 180SELECT '2'::seg >> '1'::seg AS bool; 181SELECT '0'::seg >> '0 .. 1'::seg AS bool; 182SELECT '1'::seg >> '0 .. 1'::seg AS bool; 183SELECT '2'::seg >> '0 .. 1'::seg AS bool; 184SELECT '0 .. 0.5'::seg >> '0 .. 1'::seg AS bool; 185SELECT '0 .. 1'::seg >> '0 .. 1'::seg AS bool; 186SELECT '0 .. 2'::seg >> '0 .. 2'::seg AS bool; 187SELECT '1 .. 2'::seg >> '0 .. 1'::seg AS bool; 188SELECT '2 .. 3'::seg >> '0 .. 1'::seg AS bool; 189 190 191-- "contained in" (the left value belongs within the interval specified in the right value): 192-- 193SELECT '0'::seg <@ '0'::seg AS bool; 194SELECT '0'::seg <@ '0 ..'::seg AS bool; 195SELECT '0'::seg <@ '.. 0'::seg AS bool; 196SELECT '0'::seg <@ '-1 .. 1'::seg AS bool; 197SELECT '0'::seg <@ '-1 .. 1'::seg AS bool; 198SELECT '-1'::seg <@ '-1 .. 1'::seg AS bool; 199SELECT '1'::seg <@ '-1 .. 1'::seg AS bool; 200SELECT '-1 .. 1'::seg <@ '-1 .. 1'::seg AS bool; 201 202-- "contains" (the left value contains the interval specified in the right value): 203-- 204SELECT '0'::seg @> '0'::seg AS bool; 205SELECT '0 .. '::seg <@ '0'::seg AS bool; 206SELECT '.. 0'::seg <@ '0'::seg AS bool; 207SELECT '-1 .. 1'::seg <@ '0'::seg AS bool; 208SELECT '0'::seg <@ '-1 .. 1'::seg AS bool; 209SELECT '-1'::seg <@ '-1 .. 1'::seg AS bool; 210SELECT '1'::seg <@ '-1 .. 1'::seg AS bool; 211 212-- Load some example data and build the index 213-- 214CREATE TABLE test_seg (s seg); 215 216\copy test_seg from 'data/test_seg.data' 217 218CREATE INDEX test_seg_ix ON test_seg USING gist (s); 219 220SET enable_indexscan = false; 221EXPLAIN (COSTS OFF) 222SELECT count(*) FROM test_seg WHERE s @> '11..11.3'; 223SELECT count(*) FROM test_seg WHERE s @> '11..11.3'; 224RESET enable_indexscan; 225 226SET enable_bitmapscan = false; 227EXPLAIN (COSTS OFF) 228SELECT count(*) FROM test_seg WHERE s @> '11..11.3'; 229SELECT count(*) FROM test_seg WHERE s @> '11..11.3'; 230RESET enable_bitmapscan; 231 232-- Test sorting 233SELECT * FROM test_seg WHERE s @> '11..11.3' GROUP BY s; 234 235-- Test functions 236SELECT seg_lower(s), seg_center(s), seg_upper(s) 237FROM test_seg WHERE s @> '11.2..11.3' OR s IS NULL ORDER BY s; 238