1-- 2-- INT2 3-- 4CREATE TABLE INT2_TBL(f1 int2); 5INSERT INTO INT2_TBL(f1) VALUES ('0 '); 6INSERT INTO INT2_TBL(f1) VALUES (' 1234 '); 7INSERT INTO INT2_TBL(f1) VALUES (' -1234'); 8INSERT INTO INT2_TBL(f1) VALUES ('34.5'); 9ERROR: invalid input syntax for integer: "34.5" 10LINE 1: INSERT INTO INT2_TBL(f1) VALUES ('34.5'); 11 ^ 12-- largest and smallest values 13INSERT INTO INT2_TBL(f1) VALUES ('32767'); 14INSERT INTO INT2_TBL(f1) VALUES ('-32767'); 15-- bad input values -- should give errors 16INSERT INTO INT2_TBL(f1) VALUES ('100000'); 17ERROR: value "100000" is out of range for type smallint 18LINE 1: INSERT INTO INT2_TBL(f1) VALUES ('100000'); 19 ^ 20INSERT INTO INT2_TBL(f1) VALUES ('asdf'); 21ERROR: invalid input syntax for integer: "asdf" 22LINE 1: INSERT INTO INT2_TBL(f1) VALUES ('asdf'); 23 ^ 24INSERT INTO INT2_TBL(f1) VALUES (' '); 25ERROR: invalid input syntax for integer: " " 26LINE 1: INSERT INTO INT2_TBL(f1) VALUES (' '); 27 ^ 28INSERT INTO INT2_TBL(f1) VALUES ('- 1234'); 29ERROR: invalid input syntax for integer: "- 1234" 30LINE 1: INSERT INTO INT2_TBL(f1) VALUES ('- 1234'); 31 ^ 32INSERT INTO INT2_TBL(f1) VALUES ('4 444'); 33ERROR: invalid input syntax for integer: "4 444" 34LINE 1: INSERT INTO INT2_TBL(f1) VALUES ('4 444'); 35 ^ 36INSERT INTO INT2_TBL(f1) VALUES ('123 dt'); 37ERROR: invalid input syntax for integer: "123 dt" 38LINE 1: INSERT INTO INT2_TBL(f1) VALUES ('123 dt'); 39 ^ 40INSERT INTO INT2_TBL(f1) VALUES (''); 41ERROR: invalid input syntax for integer: "" 42LINE 1: INSERT INTO INT2_TBL(f1) VALUES (''); 43 ^ 44SELECT '' AS five, * FROM INT2_TBL; 45 five | f1 46------+-------- 47 | 0 48 | 1234 49 | -1234 50 | 32767 51 | -32767 52(5 rows) 53 54SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int2 '0'; 55 four | f1 56------+-------- 57 | 1234 58 | -1234 59 | 32767 60 | -32767 61(4 rows) 62 63SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int4 '0'; 64 four | f1 65------+-------- 66 | 1234 67 | -1234 68 | 32767 69 | -32767 70(4 rows) 71 72SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int2 '0'; 73 one | f1 74-----+---- 75 | 0 76(1 row) 77 78SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int4 '0'; 79 one | f1 80-----+---- 81 | 0 82(1 row) 83 84SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int2 '0'; 85 two | f1 86-----+-------- 87 | -1234 88 | -32767 89(2 rows) 90 91SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int4 '0'; 92 two | f1 93-----+-------- 94 | -1234 95 | -32767 96(2 rows) 97 98SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int2 '0'; 99 three | f1 100-------+-------- 101 | 0 102 | -1234 103 | -32767 104(3 rows) 105 106SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int4 '0'; 107 three | f1 108-------+-------- 109 | 0 110 | -1234 111 | -32767 112(3 rows) 113 114SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int2 '0'; 115 two | f1 116-----+------- 117 | 1234 118 | 32767 119(2 rows) 120 121SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int4 '0'; 122 two | f1 123-----+------- 124 | 1234 125 | 32767 126(2 rows) 127 128SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int2 '0'; 129 three | f1 130-------+------- 131 | 0 132 | 1234 133 | 32767 134(3 rows) 135 136SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int4 '0'; 137 three | f1 138-------+------- 139 | 0 140 | 1234 141 | 32767 142(3 rows) 143 144-- positive odds 145SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % int2 '2') = int2 '1'; 146 one | f1 147-----+------- 148 | 32767 149(1 row) 150 151-- any evens 152SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % int4 '2') = int2 '0'; 153 three | f1 154-------+------- 155 | 0 156 | 1234 157 | -1234 158(3 rows) 159 160SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT2_TBL i; 161ERROR: smallint out of range 162SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT2_TBL i 163WHERE abs(f1) < 16384; 164 five | f1 | x 165------+-------+------- 166 | 0 | 0 167 | 1234 | 2468 168 | -1234 | -2468 169(3 rows) 170 171SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT2_TBL i; 172 five | f1 | x 173------+--------+-------- 174 | 0 | 0 175 | 1234 | 2468 176 | -1234 | -2468 177 | 32767 | 65534 178 | -32767 | -65534 179(5 rows) 180 181SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT2_TBL i; 182ERROR: smallint out of range 183SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT2_TBL i 184WHERE f1 < 32766; 185 five | f1 | x 186------+--------+-------- 187 | 0 | 2 188 | 1234 | 1236 189 | -1234 | -1232 190 | -32767 | -32765 191(4 rows) 192 193SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT2_TBL i; 194 five | f1 | x 195------+--------+-------- 196 | 0 | 2 197 | 1234 | 1236 198 | -1234 | -1232 199 | 32767 | 32769 200 | -32767 | -32765 201(5 rows) 202 203SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT2_TBL i; 204ERROR: smallint out of range 205SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT2_TBL i 206WHERE f1 > -32767; 207 five | f1 | x 208------+-------+------- 209 | 0 | -2 210 | 1234 | 1232 211 | -1234 | -1236 212 | 32767 | 32765 213(4 rows) 214 215SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT2_TBL i; 216 five | f1 | x 217------+--------+-------- 218 | 0 | -2 219 | 1234 | 1232 220 | -1234 | -1236 221 | 32767 | 32765 222 | -32767 | -32769 223(5 rows) 224 225SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT2_TBL i; 226 five | f1 | x 227------+--------+-------- 228 | 0 | 0 229 | 1234 | 617 230 | -1234 | -617 231 | 32767 | 16383 232 | -32767 | -16383 233(5 rows) 234 235SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT2_TBL i; 236 five | f1 | x 237------+--------+-------- 238 | 0 | 0 239 | 1234 | 617 240 | -1234 | -617 241 | 32767 | 16383 242 | -32767 | -16383 243(5 rows) 244 245-- corner cases 246SELECT (-1::int2<<15)::text; 247 text 248-------- 249 -32768 250(1 row) 251 252SELECT ((-1::int2<<15)+1::int2)::text; 253 text 254-------- 255 -32767 256(1 row) 257 258-- check sane handling of INT16_MIN overflow cases 259SELECT (-32768)::int2 * (-1)::int2; 260ERROR: smallint out of range 261SELECT (-32768)::int2 / (-1)::int2; 262ERROR: smallint out of range 263SELECT (-32768)::int2 % (-1)::int2; 264 ?column? 265---------- 266 0 267(1 row) 268 269-- check rounding when casting from float 270SELECT x, x::int2 AS int2_value 271FROM (VALUES (-2.5::float8), 272 (-1.5::float8), 273 (-0.5::float8), 274 (0.0::float8), 275 (0.5::float8), 276 (1.5::float8), 277 (2.5::float8)) t(x); 278 x | int2_value 279------+------------ 280 -2.5 | -2 281 -1.5 | -2 282 -0.5 | 0 283 0 | 0 284 0.5 | 0 285 1.5 | 2 286 2.5 | 2 287(7 rows) 288 289-- check rounding when casting from numeric 290SELECT x, x::int2 AS int2_value 291FROM (VALUES (-2.5::numeric), 292 (-1.5::numeric), 293 (-0.5::numeric), 294 (0.0::numeric), 295 (0.5::numeric), 296 (1.5::numeric), 297 (2.5::numeric)) t(x); 298 x | int2_value 299------+------------ 300 -2.5 | -3 301 -1.5 | -2 302 -0.5 | -1 303 0.0 | 0 304 0.5 | 1 305 1.5 | 2 306 2.5 | 3 307(7 rows) 308 309