int4.out 6.36 KB
Newer Older
1 2 3 4 5 6 7 8 9 10
--
-- INT4
-- WARNING: int4 operators never check for over/underflow!
-- Some of these answers are consequently numerically incorrect.
--
CREATE TABLE INT4_TBL(f1 int4);
INSERT INTO INT4_TBL(f1) VALUES ('0');
INSERT INTO INT4_TBL(f1) VALUES ('123456');
INSERT INTO INT4_TBL(f1) VALUES ('-123456');
INSERT INTO INT4_TBL(f1) VALUES ('34.5');
11
ERROR:  invalid input syntax for integer: "34.5"
12 13 14 15 16
-- largest and smallest values 
INSERT INTO INT4_TBL(f1) VALUES ('2147483647');
INSERT INTO INT4_TBL(f1) VALUES ('-2147483647');
-- bad input values -- should give warnings 
INSERT INTO INT4_TBL(f1) VALUES ('1000000000000');
17
ERROR:  1000000000000 is out of range for int4
18
INSERT INTO INT4_TBL(f1) VALUES ('asdf');
19
ERROR:  invalid input syntax for integer: "asdf"
20 21 22 23 24 25 26 27
SELECT '' AS five, INT4_TBL.*;
 five |     f1      
------+-------------
      |           0
      |      123456
      |     -123456
      |  2147483647
      | -2147483647
28 29
(5 rows)

30 31 32 33 34 35 36
SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int2 '0';
 four |     f1      
------+-------------
      |      123456
      |     -123456
      |  2147483647
      | -2147483647
37 38
(4 rows)

39 40 41 42 43 44 45
SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int4 '0';
 four |     f1      
------+-------------
      |      123456
      |     -123456
      |  2147483647
      | -2147483647
46 47
(4 rows)

48 49 50 51
SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int2 '0';
 one | f1 
-----+----
     |  0
52 53
(1 row)

54 55 56 57
SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int4 '0';
 one | f1 
-----+----
     |  0
58 59
(1 row)

60 61 62 63 64
SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int2 '0';
 two |     f1      
-----+-------------
     |     -123456
     | -2147483647
65 66
(2 rows)

67 68 69 70 71
SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int4 '0';
 two |     f1      
-----+-------------
     |     -123456
     | -2147483647
72 73
(2 rows)

74 75 76 77 78 79
SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int2 '0';
 three |     f1      
-------+-------------
       |           0
       |     -123456
       | -2147483647
80 81
(3 rows)

82 83 84 85 86 87
SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int4 '0';
 three |     f1      
-------+-------------
       |           0
       |     -123456
       | -2147483647
88 89
(3 rows)

90 91 92 93 94
SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int2 '0';
 two |     f1     
-----+------------
     |     123456
     | 2147483647
95 96
(2 rows)

97 98 99 100 101
SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int4 '0';
 two |     f1     
-----+------------
     |     123456
     | 2147483647
102 103
(2 rows)

104 105 106 107 108 109
SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int2 '0';
 three |     f1     
-------+------------
       |          0
       |     123456
       | 2147483647
110 111
(3 rows)

112 113 114 115 116 117
SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0';
 three |     f1     
-------+------------
       |          0
       |     123456
       | 2147483647
118 119
(3 rows)

120 121 122 123 124
-- positive odds 
SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
 one |     f1     
-----+------------
     | 2147483647
125 126
(1 row)

127 128 129 130 131 132 133
-- any evens 
SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0';
 three |   f1    
-------+---------
       |       0
       |  123456
       | -123456
134 135
(3 rows)

136 137 138 139 140 141 142 143
SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i;
 five |     f1      |    x    
------+-------------+---------
      |           0 |       0
      |      123456 |  246912
      |     -123456 | -246912
      |  2147483647 |      -2
      | -2147483647 |       2
144 145
(5 rows)

146 147 148 149 150 151 152 153
SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i;
 five |     f1      |    x    
------+-------------+---------
      |           0 |       0
      |      123456 |  246912
      |     -123456 | -246912
      |  2147483647 |      -2
      | -2147483647 |       2
154 155
(5 rows)

156 157 158 159 160 161 162 163
SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i;
 five |     f1      |      x      
------+-------------+-------------
      |           0 |           2
      |      123456 |      123458
      |     -123456 |     -123454
      |  2147483647 | -2147483647
      | -2147483647 | -2147483645
164 165
(5 rows)

166 167 168 169 170 171 172 173
SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i;
 five |     f1      |      x      
------+-------------+-------------
      |           0 |           2
      |      123456 |      123458
      |     -123456 |     -123454
      |  2147483647 | -2147483647
      | -2147483647 | -2147483645
174 175
(5 rows)

176 177 178 179 180 181 182 183
SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i;
 five |     f1      |     x      
------+-------------+------------
      |           0 |         -2
      |      123456 |     123454
      |     -123456 |    -123458
      |  2147483647 | 2147483645
      | -2147483647 | 2147483647
184 185
(5 rows)

186 187 188 189 190 191 192 193
SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i;
 five |     f1      |     x      
------+-------------+------------
      |           0 |         -2
      |      123456 |     123454
      |     -123456 |    -123458
      |  2147483647 | 2147483645
      | -2147483647 | 2147483647
194 195
(5 rows)

196 197 198 199 200 201 202 203
SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT4_TBL i;
 five |     f1      |      x      
------+-------------+-------------
      |           0 |           0
      |      123456 |       61728
      |     -123456 |      -61728
      |  2147483647 |  1073741823
      | -2147483647 | -1073741823
204 205
(5 rows)

206 207 208 209 210 211 212 213
SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT4_TBL i;
 five |     f1      |      x      
------+-------------+-------------
      |           0 |           0
      |      123456 |       61728
      |     -123456 |      -61728
      |  2147483647 |  1073741823
      | -2147483647 | -1073741823
214 215
(5 rows)

216 217 218 219 220 221 222 223
--
-- more complex expressions
--
-- variations on unary minus parsing
SELECT -2+3 AS one;
 one 
-----
   1
224 225
(1 row)

226 227
SELECT 4-2 AS two;
 two 
228
-----
229
   2
230 231
(1 row)

232 233 234 235
SELECT 2- -1 AS three;
 three 
-------
     3
236 237
(1 row)

238 239 240 241
SELECT 2 - -2 AS four;
 four 
------
    4
242 243
(1 row)

244 245 246 247
SELECT int2 '2' * int2 '2' = int2 '16' / int2 '4' AS true;
 true 
------
 t
248 249
(1 row)

250 251 252 253
SELECT int4 '2' * int2 '2' = int2 '16' / int4 '4' AS true;
 true 
------
 t
254 255
(1 row)

256 257 258 259
SELECT int2 '2' * int4 '2' = int4 '16' / int2 '4' AS true;
 true 
------
 t
260 261
(1 row)

262 263 264 265
SELECT int4 '1000' < int4 '999' AS false;
 false 
-------
 f
266 267
(1 row)

268 269 270 271
SELECT 4! AS twenty_four;
 twenty_four 
-------------
          24
272 273
(1 row)

274 275 276 277
SELECT !!3 AS six;
 six 
-----
   6
278 279
(1 row)

280 281
SELECT 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 AS ten;
 ten 
282
-----
283
  10
284 285
(1 row)

286 287 288 289
SELECT 2 + 2 / 2 AS three;
 three 
-------
     3
290 291
(1 row)

292 293
SELECT (2 + 2) / 2 AS two;
 two 
294
-----
295
   2
296 297
(1 row)