1-- 2-- INT4 3-- 4CREATE TABLE INT4_TBL(f1 int4); 5INSERT INTO INT4_TBL(f1) VALUES (' 0 '); 6INSERT INTO INT4_TBL(f1) VALUES ('123456 '); 7INSERT INTO INT4_TBL(f1) VALUES (' -123456'); 8INSERT INTO INT4_TBL(f1) VALUES ('34.5'); 9ERROR: invalid input syntax for integer: "34.5" 10LINE 1: INSERT INTO INT4_TBL(f1) VALUES ('34.5'); 11 ^ 12-- largest and smallest values 13INSERT INTO INT4_TBL(f1) VALUES ('2147483647'); 14INSERT INTO INT4_TBL(f1) VALUES ('-2147483647'); 15-- bad input values -- should give errors 16INSERT INTO INT4_TBL(f1) VALUES ('1000000000000'); 17ERROR: value "1000000000000" is out of range for type integer 18LINE 1: INSERT INTO INT4_TBL(f1) VALUES ('1000000000000'); 19 ^ 20INSERT INTO INT4_TBL(f1) VALUES ('asdf'); 21ERROR: invalid input syntax for integer: "asdf" 22LINE 1: INSERT INTO INT4_TBL(f1) VALUES ('asdf'); 23 ^ 24INSERT INTO INT4_TBL(f1) VALUES (' '); 25ERROR: invalid input syntax for integer: " " 26LINE 1: INSERT INTO INT4_TBL(f1) VALUES (' '); 27 ^ 28INSERT INTO INT4_TBL(f1) VALUES (' asdf '); 29ERROR: invalid input syntax for integer: " asdf " 30LINE 1: INSERT INTO INT4_TBL(f1) VALUES (' asdf '); 31 ^ 32INSERT INTO INT4_TBL(f1) VALUES ('- 1234'); 33ERROR: invalid input syntax for integer: "- 1234" 34LINE 1: INSERT INTO INT4_TBL(f1) VALUES ('- 1234'); 35 ^ 36INSERT INTO INT4_TBL(f1) VALUES ('123 5'); 37ERROR: invalid input syntax for integer: "123 5" 38LINE 1: INSERT INTO INT4_TBL(f1) VALUES ('123 5'); 39 ^ 40INSERT INTO INT4_TBL(f1) VALUES (''); 41ERROR: invalid input syntax for integer: "" 42LINE 1: INSERT INTO INT4_TBL(f1) VALUES (''); 43 ^ 44SELECT '' AS five, * FROM INT4_TBL; 45 five | f1 46------+------------- 47 | 0 48 | 123456 49 | -123456 50 | 2147483647 51 | -2147483647 52(5 rows) 53 54SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int2 '0'; 55 four | f1 56------+------------- 57 | 123456 58 | -123456 59 | 2147483647 60 | -2147483647 61(4 rows) 62 63SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int4 '0'; 64 four | f1 65------+------------- 66 | 123456 67 | -123456 68 | 2147483647 69 | -2147483647 70(4 rows) 71 72SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int2 '0'; 73 one | f1 74-----+---- 75 | 0 76(1 row) 77 78SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int4 '0'; 79 one | f1 80-----+---- 81 | 0 82(1 row) 83 84SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int2 '0'; 85 two | f1 86-----+------------- 87 | -123456 88 | -2147483647 89(2 rows) 90 91SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int4 '0'; 92 two | f1 93-----+------------- 94 | -123456 95 | -2147483647 96(2 rows) 97 98SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int2 '0'; 99 three | f1 100-------+------------- 101 | 0 102 | -123456 103 | -2147483647 104(3 rows) 105 106SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int4 '0'; 107 three | f1 108-------+------------- 109 | 0 110 | -123456 111 | -2147483647 112(3 rows) 113 114SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int2 '0'; 115 two | f1 116-----+------------ 117 | 123456 118 | 2147483647 119(2 rows) 120 121SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int4 '0'; 122 two | f1 123-----+------------ 124 | 123456 125 | 2147483647 126(2 rows) 127 128SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int2 '0'; 129 three | f1 130-------+------------ 131 | 0 132 | 123456 133 | 2147483647 134(3 rows) 135 136SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0'; 137 three | f1 138-------+------------ 139 | 0 140 | 123456 141 | 2147483647 142(3 rows) 143 144-- positive odds 145SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1'; 146 one | f1 147-----+------------ 148 | 2147483647 149(1 row) 150 151-- any evens 152SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0'; 153 three | f1 154-------+--------- 155 | 0 156 | 123456 157 | -123456 158(3 rows) 159 160SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i; 161ERROR: integer out of range 162SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i 163WHERE abs(f1) < 1073741824; 164 five | f1 | x 165------+---------+--------- 166 | 0 | 0 167 | 123456 | 246912 168 | -123456 | -246912 169(3 rows) 170 171SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i; 172ERROR: integer out of range 173SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i 174WHERE abs(f1) < 1073741824; 175 five | f1 | x 176------+---------+--------- 177 | 0 | 0 178 | 123456 | 246912 179 | -123456 | -246912 180(3 rows) 181 182SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i; 183ERROR: integer out of range 184SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i 185WHERE f1 < 2147483646; 186 five | f1 | x 187------+-------------+------------- 188 | 0 | 2 189 | 123456 | 123458 190 | -123456 | -123454 191 | -2147483647 | -2147483645 192(4 rows) 193 194SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i; 195ERROR: integer out of range 196SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i 197WHERE f1 < 2147483646; 198 five | f1 | x 199------+-------------+------------- 200 | 0 | 2 201 | 123456 | 123458 202 | -123456 | -123454 203 | -2147483647 | -2147483645 204(4 rows) 205 206SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i; 207ERROR: integer out of range 208SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i 209WHERE f1 > -2147483647; 210 five | f1 | x 211------+------------+------------ 212 | 0 | -2 213 | 123456 | 123454 214 | -123456 | -123458 215 | 2147483647 | 2147483645 216(4 rows) 217 218SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i; 219ERROR: integer out of range 220SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i 221WHERE f1 > -2147483647; 222 five | f1 | x 223------+------------+------------ 224 | 0 | -2 225 | 123456 | 123454 226 | -123456 | -123458 227 | 2147483647 | 2147483645 228(4 rows) 229 230SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT4_TBL i; 231 five | f1 | x 232------+-------------+------------- 233 | 0 | 0 234 | 123456 | 61728 235 | -123456 | -61728 236 | 2147483647 | 1073741823 237 | -2147483647 | -1073741823 238(5 rows) 239 240SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT4_TBL i; 241 five | f1 | x 242------+-------------+------------- 243 | 0 | 0 244 | 123456 | 61728 245 | -123456 | -61728 246 | 2147483647 | 1073741823 247 | -2147483647 | -1073741823 248(5 rows) 249 250-- 251-- more complex expressions 252-- 253-- variations on unary minus parsing 254SELECT -2+3 AS one; 255 one 256----- 257 1 258(1 row) 259 260SELECT 4-2 AS two; 261 two 262----- 263 2 264(1 row) 265 266SELECT 2- -1 AS three; 267 three 268------- 269 3 270(1 row) 271 272SELECT 2 - -2 AS four; 273 four 274------ 275 4 276(1 row) 277 278SELECT int2 '2' * int2 '2' = int2 '16' / int2 '4' AS true; 279 true 280------ 281 t 282(1 row) 283 284SELECT int4 '2' * int2 '2' = int2 '16' / int4 '4' AS true; 285 true 286------ 287 t 288(1 row) 289 290SELECT int2 '2' * int4 '2' = int4 '16' / int2 '4' AS true; 291 true 292------ 293 t 294(1 row) 295 296SELECT int4 '1000' < int4 '999' AS false; 297 false 298------- 299 f 300(1 row) 301 302SELECT 4! AS twenty_four; 303 twenty_four 304------------- 305 24 306(1 row) 307 308SELECT !!3 AS six; 309 six 310----- 311 6 312(1 row) 313 314SELECT 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 AS ten; 315 ten 316----- 317 10 318(1 row) 319 320SELECT 2 + 2 / 2 AS three; 321 three 322------- 323 3 324(1 row) 325 326SELECT (2 + 2) / 2 AS two; 327 two 328----- 329 2 330(1 row) 331 332-- corner case 333SELECT (-1::int4<<31)::text; 334 text 335------------- 336 -2147483648 337(1 row) 338 339SELECT ((-1::int4<<31)+1)::text; 340 text 341------------- 342 -2147483647 343(1 row) 344 345-- check sane handling of INT_MIN overflow cases 346SELECT (-2147483648)::int4 * (-1)::int4; 347ERROR: integer out of range 348SELECT (-2147483648)::int4 / (-1)::int4; 349ERROR: integer out of range 350SELECT (-2147483648)::int4 % (-1)::int4; 351 ?column? 352---------- 353 0 354(1 row) 355 356SELECT (-2147483648)::int4 * (-1)::int2; 357ERROR: integer out of range 358SELECT (-2147483648)::int4 / (-1)::int2; 359ERROR: integer out of range 360SELECT (-2147483648)::int4 % (-1)::int2; 361 ?column? 362---------- 363 0 364(1 row) 365 366-- check rounding when casting from float 367SELECT x, x::int4 AS int4_value 368FROM (VALUES (-2.5::float8), 369 (-1.5::float8), 370 (-0.5::float8), 371 (0.0::float8), 372 (0.5::float8), 373 (1.5::float8), 374 (2.5::float8)) t(x); 375 x | int4_value 376------+------------ 377 -2.5 | -2 378 -1.5 | -2 379 -0.5 | 0 380 0 | 0 381 0.5 | 0 382 1.5 | 2 383 2.5 | 2 384(7 rows) 385 386-- check rounding when casting from numeric 387SELECT x, x::int4 AS int4_value 388FROM (VALUES (-2.5::numeric), 389 (-1.5::numeric), 390 (-0.5::numeric), 391 (0.0::numeric), 392 (0.5::numeric), 393 (1.5::numeric), 394 (2.5::numeric)) t(x); 395 x | int4_value 396------+------------ 397 -2.5 | -3 398 -1.5 | -2 399 -0.5 | -1 400 0.0 | 0 401 0.5 | 1 402 1.5 | 2 403 2.5 | 3 404(7 rows) 405 406