1-- 2-- INT2 3-- 4 5CREATE TABLE INT2_TBL(f1 int2); 6 7INSERT INTO INT2_TBL(f1) VALUES ('0 '); 8 9INSERT INTO INT2_TBL(f1) VALUES (' 1234 '); 10 11INSERT INTO INT2_TBL(f1) VALUES (' -1234'); 12 13INSERT INTO INT2_TBL(f1) VALUES ('34.5'); 14 15-- largest and smallest values 16INSERT INTO INT2_TBL(f1) VALUES ('32767'); 17 18INSERT INTO INT2_TBL(f1) VALUES ('-32767'); 19 20-- bad input values -- should give errors 21INSERT INTO INT2_TBL(f1) VALUES ('100000'); 22INSERT INTO INT2_TBL(f1) VALUES ('asdf'); 23INSERT INTO INT2_TBL(f1) VALUES (' '); 24INSERT INTO INT2_TBL(f1) VALUES ('- 1234'); 25INSERT INTO INT2_TBL(f1) VALUES ('4 444'); 26INSERT INTO INT2_TBL(f1) VALUES ('123 dt'); 27INSERT INTO INT2_TBL(f1) VALUES (''); 28 29 30SELECT '' AS five, * FROM INT2_TBL; 31 32SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int2 '0'; 33 34SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int4 '0'; 35 36SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int2 '0'; 37 38SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int4 '0'; 39 40SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int2 '0'; 41 42SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int4 '0'; 43 44SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int2 '0'; 45 46SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int4 '0'; 47 48SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int2 '0'; 49 50SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int4 '0'; 51 52SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int2 '0'; 53 54SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int4 '0'; 55 56-- positive odds 57SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % int2 '2') = int2 '1'; 58 59-- any evens 60SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % int4 '2') = int2 '0'; 61 62SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT2_TBL i; 63 64SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT2_TBL i 65WHERE abs(f1) < 16384; 66 67SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT2_TBL i; 68 69SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT2_TBL i; 70 71SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT2_TBL i 72WHERE f1 < 32766; 73 74SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT2_TBL i; 75 76SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT2_TBL i; 77 78SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT2_TBL i 79WHERE f1 > -32767; 80 81SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT2_TBL i; 82 83SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT2_TBL i; 84 85SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT2_TBL i; 86 87-- corner cases 88SELECT (-1::int2<<15)::text; 89SELECT ((-1::int2<<15)+1::int2)::text; 90 91-- check sane handling of INT16_MIN overflow cases 92SELECT (-32768)::int2 * (-1)::int2; 93SELECT (-32768)::int2 / (-1)::int2; 94SELECT (-32768)::int2 % (-1)::int2; 95 96-- check rounding when casting from float 97SELECT x, x::int2 AS int2_value 98FROM (VALUES (-2.5::float8), 99 (-1.5::float8), 100 (-0.5::float8), 101 (0.0::float8), 102 (0.5::float8), 103 (1.5::float8), 104 (2.5::float8)) t(x); 105 106-- check rounding when casting from numeric 107SELECT x, x::int2 AS int2_value 108FROM (VALUES (-2.5::numeric), 109 (-1.5::numeric), 110 (-0.5::numeric), 111 (0.0::numeric), 112 (0.5::numeric), 113 (1.5::numeric), 114 (2.5::numeric)) t(x); 115