1-- 2-- INT8 3-- Test int8 64-bit integers. 4-- 5CREATE TABLE INT8_TBL(q1 int8, q2 int8); 6INSERT INTO INT8_TBL VALUES(' 123 ',' 456'); 7INSERT INTO INT8_TBL VALUES('123 ','4567890123456789'); 8INSERT INTO INT8_TBL VALUES('4567890123456789','123'); 9INSERT INTO INT8_TBL VALUES(+4567890123456789,'4567890123456789'); 10INSERT INTO INT8_TBL VALUES('+4567890123456789','-4567890123456789'); 11-- bad inputs 12INSERT INTO INT8_TBL(q1) VALUES (' '); 13ERROR: invalid input syntax for integer: " " 14LINE 1: INSERT INTO INT8_TBL(q1) VALUES (' '); 15 ^ 16INSERT INTO INT8_TBL(q1) VALUES ('xxx'); 17ERROR: invalid input syntax for integer: "xxx" 18LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('xxx'); 19 ^ 20INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485'); 21ERROR: value "3908203590239580293850293850329485" is out of range for type bigint 22LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('39082035902395802938502938... 23 ^ 24INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934'); 25ERROR: value "-1204982019841029840928340329840934" is out of range for type bigint 26LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340... 27 ^ 28INSERT INTO INT8_TBL(q1) VALUES ('- 123'); 29ERROR: invalid input syntax for integer: "- 123" 30LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('- 123'); 31 ^ 32INSERT INTO INT8_TBL(q1) VALUES (' 345 5'); 33ERROR: invalid input syntax for integer: " 345 5" 34LINE 1: INSERT INTO INT8_TBL(q1) VALUES (' 345 5'); 35 ^ 36INSERT INTO INT8_TBL(q1) VALUES (''); 37ERROR: invalid input syntax for integer: "" 38LINE 1: INSERT INTO INT8_TBL(q1) VALUES (''); 39 ^ 40SELECT * FROM INT8_TBL; 41 q1 | q2 42------------------+------------------- 43 123 | 456 44 123 | 4567890123456789 45 4567890123456789 | 123 46 4567890123456789 | 4567890123456789 47 4567890123456789 | -4567890123456789 48(5 rows) 49 50-- int8/int8 cmp 51SELECT * FROM INT8_TBL WHERE q2 = 4567890123456789; 52 q1 | q2 53------------------+------------------ 54 123 | 4567890123456789 55 4567890123456789 | 4567890123456789 56(2 rows) 57 58SELECT * FROM INT8_TBL WHERE q2 <> 4567890123456789; 59 q1 | q2 60------------------+------------------- 61 123 | 456 62 4567890123456789 | 123 63 4567890123456789 | -4567890123456789 64(3 rows) 65 66SELECT * FROM INT8_TBL WHERE q2 < 4567890123456789; 67 q1 | q2 68------------------+------------------- 69 123 | 456 70 4567890123456789 | 123 71 4567890123456789 | -4567890123456789 72(3 rows) 73 74SELECT * FROM INT8_TBL WHERE q2 > 4567890123456789; 75 q1 | q2 76----+---- 77(0 rows) 78 79SELECT * FROM INT8_TBL WHERE q2 <= 4567890123456789; 80 q1 | q2 81------------------+------------------- 82 123 | 456 83 123 | 4567890123456789 84 4567890123456789 | 123 85 4567890123456789 | 4567890123456789 86 4567890123456789 | -4567890123456789 87(5 rows) 88 89SELECT * FROM INT8_TBL WHERE q2 >= 4567890123456789; 90 q1 | q2 91------------------+------------------ 92 123 | 4567890123456789 93 4567890123456789 | 4567890123456789 94(2 rows) 95 96-- int8/int4 cmp 97SELECT * FROM INT8_TBL WHERE q2 = 456; 98 q1 | q2 99-----+----- 100 123 | 456 101(1 row) 102 103SELECT * FROM INT8_TBL WHERE q2 <> 456; 104 q1 | q2 105------------------+------------------- 106 123 | 4567890123456789 107 4567890123456789 | 123 108 4567890123456789 | 4567890123456789 109 4567890123456789 | -4567890123456789 110(4 rows) 111 112SELECT * FROM INT8_TBL WHERE q2 < 456; 113 q1 | q2 114------------------+------------------- 115 4567890123456789 | 123 116 4567890123456789 | -4567890123456789 117(2 rows) 118 119SELECT * FROM INT8_TBL WHERE q2 > 456; 120 q1 | q2 121------------------+------------------ 122 123 | 4567890123456789 123 4567890123456789 | 4567890123456789 124(2 rows) 125 126SELECT * FROM INT8_TBL WHERE q2 <= 456; 127 q1 | q2 128------------------+------------------- 129 123 | 456 130 4567890123456789 | 123 131 4567890123456789 | -4567890123456789 132(3 rows) 133 134SELECT * FROM INT8_TBL WHERE q2 >= 456; 135 q1 | q2 136------------------+------------------ 137 123 | 456 138 123 | 4567890123456789 139 4567890123456789 | 4567890123456789 140(3 rows) 141 142-- int4/int8 cmp 143SELECT * FROM INT8_TBL WHERE 123 = q1; 144 q1 | q2 145-----+------------------ 146 123 | 456 147 123 | 4567890123456789 148(2 rows) 149 150SELECT * FROM INT8_TBL WHERE 123 <> q1; 151 q1 | q2 152------------------+------------------- 153 4567890123456789 | 123 154 4567890123456789 | 4567890123456789 155 4567890123456789 | -4567890123456789 156(3 rows) 157 158SELECT * FROM INT8_TBL WHERE 123 < q1; 159 q1 | q2 160------------------+------------------- 161 4567890123456789 | 123 162 4567890123456789 | 4567890123456789 163 4567890123456789 | -4567890123456789 164(3 rows) 165 166SELECT * FROM INT8_TBL WHERE 123 > q1; 167 q1 | q2 168----+---- 169(0 rows) 170 171SELECT * FROM INT8_TBL WHERE 123 <= q1; 172 q1 | q2 173------------------+------------------- 174 123 | 456 175 123 | 4567890123456789 176 4567890123456789 | 123 177 4567890123456789 | 4567890123456789 178 4567890123456789 | -4567890123456789 179(5 rows) 180 181SELECT * FROM INT8_TBL WHERE 123 >= q1; 182 q1 | q2 183-----+------------------ 184 123 | 456 185 123 | 4567890123456789 186(2 rows) 187 188-- int8/int2 cmp 189SELECT * FROM INT8_TBL WHERE q2 = '456'::int2; 190 q1 | q2 191-----+----- 192 123 | 456 193(1 row) 194 195SELECT * FROM INT8_TBL WHERE q2 <> '456'::int2; 196 q1 | q2 197------------------+------------------- 198 123 | 4567890123456789 199 4567890123456789 | 123 200 4567890123456789 | 4567890123456789 201 4567890123456789 | -4567890123456789 202(4 rows) 203 204SELECT * FROM INT8_TBL WHERE q2 < '456'::int2; 205 q1 | q2 206------------------+------------------- 207 4567890123456789 | 123 208 4567890123456789 | -4567890123456789 209(2 rows) 210 211SELECT * FROM INT8_TBL WHERE q2 > '456'::int2; 212 q1 | q2 213------------------+------------------ 214 123 | 4567890123456789 215 4567890123456789 | 4567890123456789 216(2 rows) 217 218SELECT * FROM INT8_TBL WHERE q2 <= '456'::int2; 219 q1 | q2 220------------------+------------------- 221 123 | 456 222 4567890123456789 | 123 223 4567890123456789 | -4567890123456789 224(3 rows) 225 226SELECT * FROM INT8_TBL WHERE q2 >= '456'::int2; 227 q1 | q2 228------------------+------------------ 229 123 | 456 230 123 | 4567890123456789 231 4567890123456789 | 4567890123456789 232(3 rows) 233 234-- int2/int8 cmp 235SELECT * FROM INT8_TBL WHERE '123'::int2 = q1; 236 q1 | q2 237-----+------------------ 238 123 | 456 239 123 | 4567890123456789 240(2 rows) 241 242SELECT * FROM INT8_TBL WHERE '123'::int2 <> q1; 243 q1 | q2 244------------------+------------------- 245 4567890123456789 | 123 246 4567890123456789 | 4567890123456789 247 4567890123456789 | -4567890123456789 248(3 rows) 249 250SELECT * FROM INT8_TBL WHERE '123'::int2 < q1; 251 q1 | q2 252------------------+------------------- 253 4567890123456789 | 123 254 4567890123456789 | 4567890123456789 255 4567890123456789 | -4567890123456789 256(3 rows) 257 258SELECT * FROM INT8_TBL WHERE '123'::int2 > q1; 259 q1 | q2 260----+---- 261(0 rows) 262 263SELECT * FROM INT8_TBL WHERE '123'::int2 <= q1; 264 q1 | q2 265------------------+------------------- 266 123 | 456 267 123 | 4567890123456789 268 4567890123456789 | 123 269 4567890123456789 | 4567890123456789 270 4567890123456789 | -4567890123456789 271(5 rows) 272 273SELECT * FROM INT8_TBL WHERE '123'::int2 >= q1; 274 q1 | q2 275-----+------------------ 276 123 | 456 277 123 | 4567890123456789 278(2 rows) 279 280SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL; 281 five | plus | minus 282------+------------------+------------------- 283 | 123 | -123 284 | 123 | -123 285 | 4567890123456789 | -4567890123456789 286 | 4567890123456789 | -4567890123456789 287 | 4567890123456789 | -4567890123456789 288(5 rows) 289 290SELECT '' AS five, q1, q2, q1 + q2 AS plus FROM INT8_TBL; 291 five | q1 | q2 | plus 292------+------------------+-------------------+------------------ 293 | 123 | 456 | 579 294 | 123 | 4567890123456789 | 4567890123456912 295 | 4567890123456789 | 123 | 4567890123456912 296 | 4567890123456789 | 4567890123456789 | 9135780246913578 297 | 4567890123456789 | -4567890123456789 | 0 298(5 rows) 299 300SELECT '' AS five, q1, q2, q1 - q2 AS minus FROM INT8_TBL; 301 five | q1 | q2 | minus 302------+------------------+-------------------+------------------- 303 | 123 | 456 | -333 304 | 123 | 4567890123456789 | -4567890123456666 305 | 4567890123456789 | 123 | 4567890123456666 306 | 4567890123456789 | 4567890123456789 | 0 307 | 4567890123456789 | -4567890123456789 | 9135780246913578 308(5 rows) 309 310SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL; 311ERROR: bigint out of range 312SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL 313 WHERE q1 < 1000 or (q2 > 0 and q2 < 1000); 314 three | q1 | q2 | multiply 315-------+------------------+------------------+-------------------- 316 | 123 | 456 | 56088 317 | 123 | 4567890123456789 | 561850485185185047 318 | 4567890123456789 | 123 | 561850485185185047 319(3 rows) 320 321SELECT '' AS five, q1, q2, q1 / q2 AS divide, q1 % q2 AS mod FROM INT8_TBL; 322 five | q1 | q2 | divide | mod 323------+------------------+-------------------+----------------+----- 324 | 123 | 456 | 0 | 123 325 | 123 | 4567890123456789 | 0 | 123 326 | 4567890123456789 | 123 | 37137318076884 | 57 327 | 4567890123456789 | 4567890123456789 | 1 | 0 328 | 4567890123456789 | -4567890123456789 | -1 | 0 329(5 rows) 330 331SELECT '' AS five, q1, float8(q1) FROM INT8_TBL; 332 five | q1 | float8 333------+------------------+---------------------- 334 | 123 | 123 335 | 123 | 123 336 | 4567890123456789 | 4.56789012345679e+15 337 | 4567890123456789 | 4.56789012345679e+15 338 | 4567890123456789 | 4.56789012345679e+15 339(5 rows) 340 341SELECT '' AS five, q2, float8(q2) FROM INT8_TBL; 342 five | q2 | float8 343------+-------------------+----------------------- 344 | 456 | 456 345 | 4567890123456789 | 4.56789012345679e+15 346 | 123 | 123 347 | 4567890123456789 | 4.56789012345679e+15 348 | -4567890123456789 | -4.56789012345679e+15 349(5 rows) 350 351SELECT 37 + q1 AS plus4 FROM INT8_TBL; 352 plus4 353------------------ 354 160 355 160 356 4567890123456826 357 4567890123456826 358 4567890123456826 359(5 rows) 360 361SELECT 37 - q1 AS minus4 FROM INT8_TBL; 362 minus4 363------------------- 364 -86 365 -86 366 -4567890123456752 367 -4567890123456752 368 -4567890123456752 369(5 rows) 370 371SELECT '' AS five, 2 * q1 AS "twice int4" FROM INT8_TBL; 372 five | twice int4 373------+------------------ 374 | 246 375 | 246 376 | 9135780246913578 377 | 9135780246913578 378 | 9135780246913578 379(5 rows) 380 381SELECT '' AS five, q1 * 2 AS "twice int4" FROM INT8_TBL; 382 five | twice int4 383------+------------------ 384 | 246 385 | 246 386 | 9135780246913578 387 | 9135780246913578 388 | 9135780246913578 389(5 rows) 390 391-- int8 op int4 392SELECT q1 + 42::int4 AS "8plus4", q1 - 42::int4 AS "8minus4", q1 * 42::int4 AS "8mul4", q1 / 42::int4 AS "8div4" FROM INT8_TBL; 393 8plus4 | 8minus4 | 8mul4 | 8div4 394------------------+------------------+--------------------+----------------- 395 165 | 81 | 5166 | 2 396 165 | 81 | 5166 | 2 397 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733 398 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733 399 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733 400(5 rows) 401 402-- int4 op int8 403SELECT 246::int4 + q1 AS "4plus8", 246::int4 - q1 AS "4minus8", 246::int4 * q1 AS "4mul8", 246::int4 / q1 AS "4div8" FROM INT8_TBL; 404 4plus8 | 4minus8 | 4mul8 | 4div8 405------------------+-------------------+---------------------+------- 406 369 | 123 | 30258 | 2 407 369 | 123 | 30258 | 2 408 4567890123457035 | -4567890123456543 | 1123700970370370094 | 0 409 4567890123457035 | -4567890123456543 | 1123700970370370094 | 0 410 4567890123457035 | -4567890123456543 | 1123700970370370094 | 0 411(5 rows) 412 413-- int8 op int2 414SELECT q1 + 42::int2 AS "8plus2", q1 - 42::int2 AS "8minus2", q1 * 42::int2 AS "8mul2", q1 / 42::int2 AS "8div2" FROM INT8_TBL; 415 8plus2 | 8minus2 | 8mul2 | 8div2 416------------------+------------------+--------------------+----------------- 417 165 | 81 | 5166 | 2 418 165 | 81 | 5166 | 2 419 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733 420 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733 421 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733 422(5 rows) 423 424-- int2 op int8 425SELECT 246::int2 + q1 AS "2plus8", 246::int2 - q1 AS "2minus8", 246::int2 * q1 AS "2mul8", 246::int2 / q1 AS "2div8" FROM INT8_TBL; 426 2plus8 | 2minus8 | 2mul8 | 2div8 427------------------+-------------------+---------------------+------- 428 369 | 123 | 30258 | 2 429 369 | 123 | 30258 | 2 430 4567890123457035 | -4567890123456543 | 1123700970370370094 | 0 431 4567890123457035 | -4567890123456543 | 1123700970370370094 | 0 432 4567890123457035 | -4567890123456543 | 1123700970370370094 | 0 433(5 rows) 434 435SELECT q2, abs(q2) FROM INT8_TBL; 436 q2 | abs 437-------------------+------------------ 438 456 | 456 439 4567890123456789 | 4567890123456789 440 123 | 123 441 4567890123456789 | 4567890123456789 442 -4567890123456789 | 4567890123456789 443(5 rows) 444 445SELECT min(q1), min(q2) FROM INT8_TBL; 446 min | min 447-----+------------------- 448 123 | -4567890123456789 449(1 row) 450 451SELECT max(q1), max(q2) FROM INT8_TBL; 452 max | max 453------------------+------------------ 454 4567890123456789 | 4567890123456789 455(1 row) 456 457-- TO_CHAR() 458-- 459SELECT '' AS to_char_1, to_char(q1, '9G999G999G999G999G999'), to_char(q2, '9,999,999,999,999,999') 460 FROM INT8_TBL; 461 to_char_1 | to_char | to_char 462-----------+------------------------+------------------------ 463 | 123 | 456 464 | 123 | 4,567,890,123,456,789 465 | 4,567,890,123,456,789 | 123 466 | 4,567,890,123,456,789 | 4,567,890,123,456,789 467 | 4,567,890,123,456,789 | -4,567,890,123,456,789 468(5 rows) 469 470SELECT '' AS to_char_2, to_char(q1, '9G999G999G999G999G999D999G999'), to_char(q2, '9,999,999,999,999,999.999,999') 471 FROM INT8_TBL; 472 to_char_2 | to_char | to_char 473-----------+--------------------------------+-------------------------------- 474 | 123.000,000 | 456.000,000 475 | 123.000,000 | 4,567,890,123,456,789.000,000 476 | 4,567,890,123,456,789.000,000 | 123.000,000 477 | 4,567,890,123,456,789.000,000 | 4,567,890,123,456,789.000,000 478 | 4,567,890,123,456,789.000,000 | -4,567,890,123,456,789.000,000 479(5 rows) 480 481SELECT '' AS to_char_3, to_char( (q1 * -1), '9999999999999999PR'), to_char( (q2 * -1), '9999999999999999.999PR') 482 FROM INT8_TBL; 483 to_char_3 | to_char | to_char 484-----------+--------------------+------------------------ 485 | <123> | <456.000> 486 | <123> | <4567890123456789.000> 487 | <4567890123456789> | <123.000> 488 | <4567890123456789> | <4567890123456789.000> 489 | <4567890123456789> | 4567890123456789.000 490(5 rows) 491 492SELECT '' AS to_char_4, to_char( (q1 * -1), '9999999999999999S'), to_char( (q2 * -1), 'S9999999999999999') 493 FROM INT8_TBL; 494 to_char_4 | to_char | to_char 495-----------+-------------------+------------------- 496 | 123- | -456 497 | 123- | -4567890123456789 498 | 4567890123456789- | -123 499 | 4567890123456789- | -4567890123456789 500 | 4567890123456789- | +4567890123456789 501(5 rows) 502 503SELECT '' AS to_char_5, to_char(q2, 'MI9999999999999999') FROM INT8_TBL; 504 to_char_5 | to_char 505-----------+------------------- 506 | 456 507 | 4567890123456789 508 | 123 509 | 4567890123456789 510 | -4567890123456789 511(5 rows) 512 513SELECT '' AS to_char_6, to_char(q2, 'FMS9999999999999999') FROM INT8_TBL; 514 to_char_6 | to_char 515-----------+------------------- 516 | +456 517 | +4567890123456789 518 | +123 519 | +4567890123456789 520 | -4567890123456789 521(5 rows) 522 523SELECT '' AS to_char_7, to_char(q2, 'FM9999999999999999THPR') FROM INT8_TBL; 524 to_char_7 | to_char 525-----------+-------------------- 526 | 456TH 527 | 4567890123456789TH 528 | 123RD 529 | 4567890123456789TH 530 | <4567890123456789> 531(5 rows) 532 533SELECT '' AS to_char_8, to_char(q2, 'SG9999999999999999th') FROM INT8_TBL; 534 to_char_8 | to_char 535-----------+--------------------- 536 | + 456th 537 | +4567890123456789th 538 | + 123rd 539 | +4567890123456789th 540 | -4567890123456789 541(5 rows) 542 543SELECT '' AS to_char_9, to_char(q2, '0999999999999999') FROM INT8_TBL; 544 to_char_9 | to_char 545-----------+------------------- 546 | 0000000000000456 547 | 4567890123456789 548 | 0000000000000123 549 | 4567890123456789 550 | -4567890123456789 551(5 rows) 552 553SELECT '' AS to_char_10, to_char(q2, 'S0999999999999999') FROM INT8_TBL; 554 to_char_10 | to_char 555------------+------------------- 556 | +0000000000000456 557 | +4567890123456789 558 | +0000000000000123 559 | +4567890123456789 560 | -4567890123456789 561(5 rows) 562 563SELECT '' AS to_char_11, to_char(q2, 'FM0999999999999999') FROM INT8_TBL; 564 to_char_11 | to_char 565------------+------------------- 566 | 0000000000000456 567 | 4567890123456789 568 | 0000000000000123 569 | 4567890123456789 570 | -4567890123456789 571(5 rows) 572 573SELECT '' AS to_char_12, to_char(q2, 'FM9999999999999999.000') FROM INT8_TBL; 574 to_char_12 | to_char 575------------+----------------------- 576 | 456.000 577 | 4567890123456789.000 578 | 123.000 579 | 4567890123456789.000 580 | -4567890123456789.000 581(5 rows) 582 583SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000') FROM INT8_TBL; 584 to_char_13 | to_char 585------------+------------------------ 586 | 456.000 587 | 4567890123456789.000 588 | 123.000 589 | 4567890123456789.000 590 | -4567890123456789.000 591(5 rows) 592 593SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL; 594 to_char_14 | to_char 595------------+-------------------- 596 | 456. 597 | 4567890123456789. 598 | 123. 599 | 4567890123456789. 600 | -4567890123456789. 601(5 rows) 602 603SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL; 604 to_char_15 | to_char 605------------+------------------------------------------- 606 | +4 5 6 . 0 0 0 607 | +4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0 608 | +1 2 3 . 0 0 0 609 | +4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0 610 | -4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0 611(5 rows) 612 613SELECT '' AS to_char_16, to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL; 614 to_char_16 | to_char 615------------+----------------------------------------------------------- 616 | text 9999 "text between quote marks" 456 617 | 45678 text 9012 9999 345 "text between quote marks" 6789 618 | text 9999 "text between quote marks" 123 619 | 45678 text 9012 9999 345 "text between quote marks" 6789 620 | -45678 text 9012 9999 345 "text between quote marks" 6789 621(5 rows) 622 623SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999') FROM INT8_TBL; 624 to_char_17 | to_char 625------------+------------------- 626 | + 456 627 | 456789+0123456789 628 | + 123 629 | 456789+0123456789 630 | 456789-0123456789 631(5 rows) 632 633-- check min/max values and overflow behavior 634select '-9223372036854775808'::int8; 635 int8 636---------------------- 637 -9223372036854775808 638(1 row) 639 640select '-9223372036854775809'::int8; 641ERROR: value "-9223372036854775809" is out of range for type bigint 642LINE 1: select '-9223372036854775809'::int8; 643 ^ 644select '9223372036854775807'::int8; 645 int8 646--------------------- 647 9223372036854775807 648(1 row) 649 650select '9223372036854775808'::int8; 651ERROR: value "9223372036854775808" is out of range for type bigint 652LINE 1: select '9223372036854775808'::int8; 653 ^ 654select -('-9223372036854775807'::int8); 655 ?column? 656--------------------- 657 9223372036854775807 658(1 row) 659 660select -('-9223372036854775808'::int8); 661ERROR: bigint out of range 662select '9223372036854775800'::int8 + '9223372036854775800'::int8; 663ERROR: bigint out of range 664select '-9223372036854775800'::int8 + '-9223372036854775800'::int8; 665ERROR: bigint out of range 666select '9223372036854775800'::int8 - '-9223372036854775800'::int8; 667ERROR: bigint out of range 668select '-9223372036854775800'::int8 - '9223372036854775800'::int8; 669ERROR: bigint out of range 670select '9223372036854775800'::int8 * '9223372036854775800'::int8; 671ERROR: bigint out of range 672select '9223372036854775800'::int8 / '0'::int8; 673ERROR: division by zero 674select '9223372036854775800'::int8 % '0'::int8; 675ERROR: division by zero 676select abs('-9223372036854775808'::int8); 677ERROR: bigint out of range 678select '9223372036854775800'::int8 + '100'::int4; 679ERROR: bigint out of range 680select '-9223372036854775800'::int8 - '100'::int4; 681ERROR: bigint out of range 682select '9223372036854775800'::int8 * '100'::int4; 683ERROR: bigint out of range 684select '100'::int4 + '9223372036854775800'::int8; 685ERROR: bigint out of range 686select '-100'::int4 - '9223372036854775800'::int8; 687ERROR: bigint out of range 688select '100'::int4 * '9223372036854775800'::int8; 689ERROR: bigint out of range 690select '9223372036854775800'::int8 + '100'::int2; 691ERROR: bigint out of range 692select '-9223372036854775800'::int8 - '100'::int2; 693ERROR: bigint out of range 694select '9223372036854775800'::int8 * '100'::int2; 695ERROR: bigint out of range 696select '-9223372036854775808'::int8 / '0'::int2; 697ERROR: division by zero 698select '100'::int2 + '9223372036854775800'::int8; 699ERROR: bigint out of range 700select '-100'::int2 - '9223372036854775800'::int8; 701ERROR: bigint out of range 702select '100'::int2 * '9223372036854775800'::int8; 703ERROR: bigint out of range 704select '100'::int2 / '0'::int8; 705ERROR: division by zero 706SELECT CAST(q1 AS int4) FROM int8_tbl WHERE q2 = 456; 707 q1 708----- 709 123 710(1 row) 711 712SELECT CAST(q1 AS int4) FROM int8_tbl WHERE q2 <> 456; 713ERROR: integer out of range 714SELECT CAST(q1 AS int2) FROM int8_tbl WHERE q2 = 456; 715 q1 716----- 717 123 718(1 row) 719 720SELECT CAST(q1 AS int2) FROM int8_tbl WHERE q2 <> 456; 721ERROR: smallint out of range 722SELECT CAST('42'::int2 AS int8), CAST('-37'::int2 AS int8); 723 int8 | int8 724------+------ 725 42 | -37 726(1 row) 727 728SELECT CAST(q1 AS float4), CAST(q2 AS float8) FROM INT8_TBL; 729 q1 | q2 730-------------+----------------------- 731 123 | 456 732 123 | 4.56789012345679e+15 733 4.56789e+15 | 123 734 4.56789e+15 | 4.56789012345679e+15 735 4.56789e+15 | -4.56789012345679e+15 736(5 rows) 737 738SELECT CAST('36854775807.0'::float4 AS int8); 739 int8 740------------- 741 36854775808 742(1 row) 743 744SELECT CAST('922337203685477580700.0'::float8 AS int8); 745ERROR: bigint out of range 746SELECT CAST(q1 AS oid) FROM INT8_TBL; 747ERROR: OID out of range 748SELECT oid::int8 FROM pg_class WHERE relname = 'pg_class'; 749 oid 750------ 751 1259 752(1 row) 753 754-- bit operations 755SELECT q1, q2, q1 & q2 AS "and", q1 | q2 AS "or", q1 # q2 AS "xor", ~q1 AS "not" FROM INT8_TBL; 756 q1 | q2 | and | or | xor | not 757------------------+-------------------+------------------+------------------+------------------+------------------- 758 123 | 456 | 72 | 507 | 435 | -124 759 123 | 4567890123456789 | 17 | 4567890123456895 | 4567890123456878 | -124 760 4567890123456789 | 123 | 17 | 4567890123456895 | 4567890123456878 | -4567890123456790 761 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 0 | -4567890123456790 762 4567890123456789 | -4567890123456789 | 1 | -1 | -2 | -4567890123456790 763(5 rows) 764 765SELECT q1, q1 << 2 AS "shl", q1 >> 3 AS "shr" FROM INT8_TBL; 766 q1 | shl | shr 767------------------+-------------------+----------------- 768 123 | 492 | 15 769 123 | 492 | 15 770 4567890123456789 | 18271560493827156 | 570986265432098 771 4567890123456789 | 18271560493827156 | 570986265432098 772 4567890123456789 | 18271560493827156 | 570986265432098 773(5 rows) 774 775-- generate_series 776SELECT * FROM generate_series('+4567890123456789'::int8, '+4567890123456799'::int8); 777 generate_series 778------------------ 779 4567890123456789 780 4567890123456790 781 4567890123456791 782 4567890123456792 783 4567890123456793 784 4567890123456794 785 4567890123456795 786 4567890123456796 787 4567890123456797 788 4567890123456798 789 4567890123456799 790(11 rows) 791 792SELECT * FROM generate_series('+4567890123456789'::int8, '+4567890123456799'::int8, 0); 793ERROR: step size cannot equal zero 794SELECT * FROM generate_series('+4567890123456789'::int8, '+4567890123456799'::int8, 2); 795 generate_series 796------------------ 797 4567890123456789 798 4567890123456791 799 4567890123456793 800 4567890123456795 801 4567890123456797 802 4567890123456799 803(6 rows) 804 805-- corner case 806SELECT (-1::int8<<63)::text; 807 text 808---------------------- 809 -9223372036854775808 810(1 row) 811 812SELECT ((-1::int8<<63)+1)::text; 813 text 814---------------------- 815 -9223372036854775807 816(1 row) 817 818-- check sane handling of INT64_MIN overflow cases 819SELECT (-9223372036854775808)::int8 * (-1)::int8; 820ERROR: bigint out of range 821SELECT (-9223372036854775808)::int8 / (-1)::int8; 822ERROR: bigint out of range 823SELECT (-9223372036854775808)::int8 % (-1)::int8; 824 ?column? 825---------- 826 0 827(1 row) 828 829SELECT (-9223372036854775808)::int8 * (-1)::int4; 830ERROR: bigint out of range 831SELECT (-9223372036854775808)::int8 / (-1)::int4; 832ERROR: bigint out of range 833SELECT (-9223372036854775808)::int8 % (-1)::int4; 834 ?column? 835---------- 836 0 837(1 row) 838 839SELECT (-9223372036854775808)::int8 * (-1)::int2; 840ERROR: bigint out of range 841SELECT (-9223372036854775808)::int8 / (-1)::int2; 842ERROR: bigint out of range 843SELECT (-9223372036854775808)::int8 % (-1)::int2; 844 ?column? 845---------- 846 0 847(1 row) 848 849-- check rounding when casting from float 850SELECT x, x::int8 AS int8_value 851FROM (VALUES (-2.5::float8), 852 (-1.5::float8), 853 (-0.5::float8), 854 (0.0::float8), 855 (0.5::float8), 856 (1.5::float8), 857 (2.5::float8)) t(x); 858 x | int8_value 859------+------------ 860 -2.5 | -2 861 -1.5 | -2 862 -0.5 | 0 863 0 | 0 864 0.5 | 0 865 1.5 | 2 866 2.5 | 2 867(7 rows) 868 869-- check rounding when casting from numeric 870SELECT x, x::int8 AS int8_value 871FROM (VALUES (-2.5::numeric), 872 (-1.5::numeric), 873 (-0.5::numeric), 874 (0.0::numeric), 875 (0.5::numeric), 876 (1.5::numeric), 877 (2.5::numeric)) t(x); 878 x | int8_value 879------+------------ 880 -2.5 | -3 881 -1.5 | -2 882 -0.5 | -1 883 0.0 | 0 884 0.5 | 1 885 1.5 | 2 886 2.5 | 3 887(7 rows) 888 889