1-- 2-- FLOAT8 3-- 4CREATE TABLE FLOAT8_TBL(f1 float8); 5INSERT INTO FLOAT8_TBL(f1) VALUES (' 0.0 '); 6INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30 '); 7INSERT INTO FLOAT8_TBL(f1) VALUES (' -34.84'); 8INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200'); 9INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200'); 10-- test for underflow and overflow handling 11SELECT '10e400'::float8; 12ERROR: "10e400" is out of range for type double precision 13LINE 1: SELECT '10e400'::float8; 14 ^ 15SELECT '-10e400'::float8; 16ERROR: "-10e400" is out of range for type double precision 17LINE 1: SELECT '-10e400'::float8; 18 ^ 19SELECT '10e-400'::float8; 20 float8 21-------- 22 0 23(1 row) 24 25SELECT '-10e-400'::float8; 26 float8 27-------- 28 0 29(1 row) 30 31-- bad input 32INSERT INTO FLOAT8_TBL(f1) VALUES (''); 33ERROR: invalid input syntax for type double precision: "" 34LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES (''); 35 ^ 36INSERT INTO FLOAT8_TBL(f1) VALUES (' '); 37ERROR: invalid input syntax for type double precision: " " 38LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES (' '); 39 ^ 40INSERT INTO FLOAT8_TBL(f1) VALUES ('xyz'); 41ERROR: invalid input syntax for type double precision: "xyz" 42LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('xyz'); 43 ^ 44INSERT INTO FLOAT8_TBL(f1) VALUES ('5.0.0'); 45ERROR: invalid input syntax for type double precision: "5.0.0" 46LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('5.0.0'); 47 ^ 48INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0'); 49ERROR: invalid input syntax for type double precision: "5 . 0" 50LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0'); 51 ^ 52INSERT INTO FLOAT8_TBL(f1) VALUES ('5. 0'); 53ERROR: invalid input syntax for type double precision: "5. 0" 54LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('5. 0'); 55 ^ 56INSERT INTO FLOAT8_TBL(f1) VALUES (' - 3'); 57ERROR: invalid input syntax for type double precision: " - 3" 58LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES (' - 3'); 59 ^ 60INSERT INTO FLOAT8_TBL(f1) VALUES ('123 5'); 61ERROR: invalid input syntax for type double precision: "123 5" 62LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('123 5'); 63 ^ 64-- special inputs 65SELECT 'NaN'::float8; 66 float8 67-------- 68 NaN 69(1 row) 70 71SELECT 'nan'::float8; 72 float8 73-------- 74 NaN 75(1 row) 76 77SELECT ' NAN '::float8; 78 float8 79-------- 80 NaN 81(1 row) 82 83SELECT 'infinity'::float8; 84 float8 85---------- 86 Infinity 87(1 row) 88 89SELECT ' -INFINiTY '::float8; 90 float8 91----------- 92 -Infinity 93(1 row) 94 95-- bad special inputs 96SELECT 'N A N'::float8; 97ERROR: invalid input syntax for type double precision: "N A N" 98LINE 1: SELECT 'N A N'::float8; 99 ^ 100SELECT 'NaN x'::float8; 101ERROR: invalid input syntax for type double precision: "NaN x" 102LINE 1: SELECT 'NaN x'::float8; 103 ^ 104SELECT ' INFINITY x'::float8; 105ERROR: invalid input syntax for type double precision: " INFINITY x" 106LINE 1: SELECT ' INFINITY x'::float8; 107 ^ 108SELECT 'Infinity'::float8 + 100.0; 109 ?column? 110---------- 111 Infinity 112(1 row) 113 114SELECT 'Infinity'::float8 / 'Infinity'::float8; 115 ?column? 116---------- 117 NaN 118(1 row) 119 120SELECT 'nan'::float8 / 'nan'::float8; 121 ?column? 122---------- 123 NaN 124(1 row) 125 126SELECT 'nan'::numeric::float8; 127 float8 128-------- 129 NaN 130(1 row) 131 132SELECT '' AS five, * FROM FLOAT8_TBL; 133 five | f1 134------+---------------------- 135 | 0 136 | 1004.3 137 | -34.84 138 | 1.2345678901234e+200 139 | 1.2345678901234e-200 140(5 rows) 141 142SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3'; 143 four | f1 144------+---------------------- 145 | 0 146 | -34.84 147 | 1.2345678901234e+200 148 | 1.2345678901234e-200 149(4 rows) 150 151SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3'; 152 one | f1 153-----+-------- 154 | 1004.3 155(1 row) 156 157SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE '1004.3' > f.f1; 158 three | f1 159-------+---------------------- 160 | 0 161 | -34.84 162 | 1.2345678901234e-200 163(3 rows) 164 165SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE f.f1 < '1004.3'; 166 three | f1 167-------+---------------------- 168 | 0 169 | -34.84 170 | 1.2345678901234e-200 171(3 rows) 172 173SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1; 174 four | f1 175------+---------------------- 176 | 0 177 | 1004.3 178 | -34.84 179 | 1.2345678901234e-200 180(4 rows) 181 182SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <= '1004.3'; 183 four | f1 184------+---------------------- 185 | 0 186 | 1004.3 187 | -34.84 188 | 1.2345678901234e-200 189(4 rows) 190 191SELECT '' AS three, f.f1, f.f1 * '-10' AS x 192 FROM FLOAT8_TBL f 193 WHERE f.f1 > '0.0'; 194 three | f1 | x 195-------+----------------------+----------------------- 196 | 1004.3 | -10043 197 | 1.2345678901234e+200 | -1.2345678901234e+201 198 | 1.2345678901234e-200 | -1.2345678901234e-199 199(3 rows) 200 201SELECT '' AS three, f.f1, f.f1 + '-10' AS x 202 FROM FLOAT8_TBL f 203 WHERE f.f1 > '0.0'; 204 three | f1 | x 205-------+----------------------+---------------------- 206 | 1004.3 | 994.3 207 | 1.2345678901234e+200 | 1.2345678901234e+200 208 | 1.2345678901234e-200 | -10 209(3 rows) 210 211SELECT '' AS three, f.f1, f.f1 / '-10' AS x 212 FROM FLOAT8_TBL f 213 WHERE f.f1 > '0.0'; 214 three | f1 | x 215-------+----------------------+----------------------- 216 | 1004.3 | -100.43 217 | 1.2345678901234e+200 | -1.2345678901234e+199 218 | 1.2345678901234e-200 | -1.2345678901234e-201 219(3 rows) 220 221SELECT '' AS three, f.f1, f.f1 - '-10' AS x 222 FROM FLOAT8_TBL f 223 WHERE f.f1 > '0.0'; 224 three | f1 | x 225-------+----------------------+---------------------- 226 | 1004.3 | 1014.3 227 | 1.2345678901234e+200 | 1.2345678901234e+200 228 | 1.2345678901234e-200 | 10 229(3 rows) 230 231SELECT '' AS one, f.f1 ^ '2.0' AS square_f1 232 FROM FLOAT8_TBL f where f.f1 = '1004.3'; 233 one | square_f1 234-----+------------ 235 | 1008618.49 236(1 row) 237 238-- absolute value 239SELECT '' AS five, f.f1, @f.f1 AS abs_f1 240 FROM FLOAT8_TBL f; 241 five | f1 | abs_f1 242------+----------------------+---------------------- 243 | 0 | 0 244 | 1004.3 | 1004.3 245 | -34.84 | 34.84 246 | 1.2345678901234e+200 | 1.2345678901234e+200 247 | 1.2345678901234e-200 | 1.2345678901234e-200 248(5 rows) 249 250-- truncate 251SELECT '' AS five, f.f1, trunc(f.f1) AS trunc_f1 252 FROM FLOAT8_TBL f; 253 five | f1 | trunc_f1 254------+----------------------+---------------------- 255 | 0 | 0 256 | 1004.3 | 1004 257 | -34.84 | -34 258 | 1.2345678901234e+200 | 1.2345678901234e+200 259 | 1.2345678901234e-200 | 0 260(5 rows) 261 262-- round 263SELECT '' AS five, f.f1, round(f.f1) AS round_f1 264 FROM FLOAT8_TBL f; 265 five | f1 | round_f1 266------+----------------------+---------------------- 267 | 0 | 0 268 | 1004.3 | 1004 269 | -34.84 | -35 270 | 1.2345678901234e+200 | 1.2345678901234e+200 271 | 1.2345678901234e-200 | 0 272(5 rows) 273 274-- ceil / ceiling 275select ceil(f1) as ceil_f1 from float8_tbl f; 276 ceil_f1 277---------------------- 278 0 279 1005 280 -34 281 1.2345678901234e+200 282 1 283(5 rows) 284 285select ceiling(f1) as ceiling_f1 from float8_tbl f; 286 ceiling_f1 287---------------------- 288 0 289 1005 290 -34 291 1.2345678901234e+200 292 1 293(5 rows) 294 295-- floor 296select floor(f1) as floor_f1 from float8_tbl f; 297 floor_f1 298---------------------- 299 0 300 1004 301 -35 302 1.2345678901234e+200 303 0 304(5 rows) 305 306-- sign 307select sign(f1) as sign_f1 from float8_tbl f; 308 sign_f1 309--------- 310 0 311 1 312 -1 313 1 314 1 315(5 rows) 316 317-- square root 318SELECT sqrt(float8 '64') AS eight; 319 eight 320------- 321 8 322(1 row) 323 324SELECT |/ float8 '64' AS eight; 325 eight 326------- 327 8 328(1 row) 329 330SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1 331 FROM FLOAT8_TBL f 332 WHERE f.f1 > '0.0'; 333 three | f1 | sqrt_f1 334-------+----------------------+----------------------- 335 | 1004.3 | 31.6906926399535 336 | 1.2345678901234e+200 | 1.11111110611109e+100 337 | 1.2345678901234e-200 | 1.11111110611109e-100 338(3 rows) 339 340-- power 341SELECT power(float8 '144', float8 '0.5'); 342 power 343------- 344 12 345(1 row) 346 347SELECT power(float8 'NaN', float8 '0.5'); 348 power 349------- 350 NaN 351(1 row) 352 353SELECT power(float8 '144', float8 'NaN'); 354 power 355------- 356 NaN 357(1 row) 358 359SELECT power(float8 'NaN', float8 'NaN'); 360 power 361------- 362 NaN 363(1 row) 364 365SELECT power(float8 '-1', float8 'NaN'); 366 power 367------- 368 NaN 369(1 row) 370 371SELECT power(float8 '1', float8 'NaN'); 372 power 373------- 374 1 375(1 row) 376 377SELECT power(float8 'NaN', float8 '0'); 378 power 379------- 380 1 381(1 row) 382 383-- take exp of ln(f.f1) 384SELECT '' AS three, f.f1, exp(ln(f.f1)) AS exp_ln_f1 385 FROM FLOAT8_TBL f 386 WHERE f.f1 > '0.0'; 387 three | f1 | exp_ln_f1 388-------+----------------------+----------------------- 389 | 1004.3 | 1004.3 390 | 1.2345678901234e+200 | 1.23456789012338e+200 391 | 1.2345678901234e-200 | 1.23456789012339e-200 392(3 rows) 393 394-- cube root 395SELECT ||/ float8 '27' AS three; 396 three 397------- 398 3 399(1 row) 400 401SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f; 402 five | f1 | cbrt_f1 403------+----------------------+---------------------- 404 | 0 | 0 405 | 1004.3 | 10.014312837827 406 | -34.84 | -3.26607421344208 407 | 1.2345678901234e+200 | 4.97933859234765e+66 408 | 1.2345678901234e-200 | 2.3112042409018e-67 409(5 rows) 410 411SELECT '' AS five, * FROM FLOAT8_TBL; 412 five | f1 413------+---------------------- 414 | 0 415 | 1004.3 416 | -34.84 417 | 1.2345678901234e+200 418 | 1.2345678901234e-200 419(5 rows) 420 421UPDATE FLOAT8_TBL 422 SET f1 = FLOAT8_TBL.f1 * '-1' 423 WHERE FLOAT8_TBL.f1 > '0.0'; 424SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; 425ERROR: value out of range: overflow 426SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; 427ERROR: value out of range: overflow 428SELECT 0 ^ 0 + 0 ^ 1 + 0 ^ 0.0 + 0 ^ 0.5; 429 ?column? 430---------- 431 2 432(1 row) 433 434SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ; 435ERROR: cannot take logarithm of zero 436SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 < '0.0' ; 437ERROR: cannot take logarithm of a negative number 438SELECT '' AS bad, exp(f.f1) from FLOAT8_TBL f; 439ERROR: value out of range: underflow 440SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f; 441ERROR: division by zero 442SELECT '' AS five, * FROM FLOAT8_TBL; 443 five | f1 444------+----------------------- 445 | 0 446 | -34.84 447 | -1004.3 448 | -1.2345678901234e+200 449 | -1.2345678901234e-200 450(5 rows) 451 452-- test for over- and underflow 453INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400'); 454ERROR: "10e400" is out of range for type double precision 455LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400'); 456 ^ 457INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400'); 458ERROR: "-10e400" is out of range for type double precision 459LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400'); 460 ^ 461INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'); 462INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400'); 463-- maintain external table consistency across platforms 464-- delete all values and reinsert well-behaved ones 465DELETE FROM FLOAT8_TBL; 466INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0'); 467INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84'); 468INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30'); 469INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200'); 470INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200'); 471SELECT '' AS five, * FROM FLOAT8_TBL; 472 five | f1 473------+----------------------- 474 | 0 475 | -34.84 476 | -1004.3 477 | -1.2345678901234e+200 478 | -1.2345678901234e-200 479(5 rows) 480 481-- test edge-case coercions to integer 482SELECT '32767.4'::float8::int2; 483 int2 484------- 485 32767 486(1 row) 487 488SELECT '32767.6'::float8::int2; 489ERROR: smallint out of range 490SELECT '-32768.4'::float8::int2; 491 int2 492-------- 493 -32768 494(1 row) 495 496SELECT '-32768.6'::float8::int2; 497ERROR: smallint out of range 498SELECT '2147483647.4'::float8::int4; 499 int4 500------------ 501 2147483647 502(1 row) 503 504SELECT '2147483647.6'::float8::int4; 505ERROR: integer out of range 506SELECT '-2147483648.4'::float8::int4; 507 int4 508------------- 509 -2147483648 510(1 row) 511 512SELECT '-2147483648.6'::float8::int4; 513ERROR: integer out of range 514SELECT '9223372036854773760'::float8::int8; 515 int8 516--------------------- 517 9223372036854773760 518(1 row) 519 520SELECT '9223372036854775807'::float8::int8; 521ERROR: bigint out of range 522SELECT '-9223372036854775808.5'::float8::int8; 523 int8 524---------------------- 525 -9223372036854775808 526(1 row) 527 528SELECT '-9223372036854780000'::float8::int8; 529ERROR: bigint out of range 530-- test exact cases for trigonometric functions in degrees 531SET extra_float_digits = 3; 532SELECT x, 533 sind(x), 534 sind(x) IN (-1,-0.5,0,0.5,1) AS sind_exact 535FROM (VALUES (0), (30), (90), (150), (180), 536 (210), (270), (330), (360)) AS t(x); 537 x | sind | sind_exact 538-----+------+------------ 539 0 | 0 | t 540 30 | 0.5 | t 541 90 | 1 | t 542 150 | 0.5 | t 543 180 | 0 | t 544 210 | -0.5 | t 545 270 | -1 | t 546 330 | -0.5 | t 547 360 | 0 | t 548(9 rows) 549 550SELECT x, 551 cosd(x), 552 cosd(x) IN (-1,-0.5,0,0.5,1) AS cosd_exact 553FROM (VALUES (0), (60), (90), (120), (180), 554 (240), (270), (300), (360)) AS t(x); 555 x | cosd | cosd_exact 556-----+------+------------ 557 0 | 1 | t 558 60 | 0.5 | t 559 90 | 0 | t 560 120 | -0.5 | t 561 180 | -1 | t 562 240 | -0.5 | t 563 270 | 0 | t 564 300 | 0.5 | t 565 360 | 1 | t 566(9 rows) 567 568SELECT x, 569 tand(x), 570 tand(x) IN ('-Infinity'::float8,-1,0, 571 1,'Infinity'::float8) AS tand_exact, 572 cotd(x), 573 cotd(x) IN ('-Infinity'::float8,-1,0, 574 1,'Infinity'::float8) AS cotd_exact 575FROM (VALUES (0), (45), (90), (135), (180), 576 (225), (270), (315), (360)) AS t(x); 577 x | tand | tand_exact | cotd | cotd_exact 578-----+-----------+------------+-----------+------------ 579 0 | 0 | t | Infinity | t 580 45 | 1 | t | 1 | t 581 90 | Infinity | t | 0 | t 582 135 | -1 | t | -1 | t 583 180 | 0 | t | -Infinity | t 584 225 | 1 | t | 1 | t 585 270 | -Infinity | t | 0 | t 586 315 | -1 | t | -1 | t 587 360 | 0 | t | Infinity | t 588(9 rows) 589 590SELECT x, 591 asind(x), 592 asind(x) IN (-90,-30,0,30,90) AS asind_exact, 593 acosd(x), 594 acosd(x) IN (0,60,90,120,180) AS acosd_exact 595FROM (VALUES (-1), (-0.5), (0), (0.5), (1)) AS t(x); 596 x | asind | asind_exact | acosd | acosd_exact 597------+-------+-------------+-------+------------- 598 -1 | -90 | t | 180 | t 599 -0.5 | -30 | t | 120 | t 600 0 | 0 | t | 90 | t 601 0.5 | 30 | t | 60 | t 602 1 | 90 | t | 0 | t 603(5 rows) 604 605SELECT x, 606 atand(x), 607 atand(x) IN (-90,-45,0,45,90) AS atand_exact 608FROM (VALUES ('-Infinity'::float8), (-1), (0), (1), 609 ('Infinity'::float8)) AS t(x); 610 x | atand | atand_exact 611-----------+-------+------------- 612 -Infinity | -90 | t 613 -1 | -45 | t 614 0 | 0 | t 615 1 | 45 | t 616 Infinity | 90 | t 617(5 rows) 618 619SELECT x, y, 620 atan2d(y, x), 621 atan2d(y, x) IN (-90,0,90,180) AS atan2d_exact 622FROM (SELECT 10*cosd(a), 10*sind(a) 623 FROM generate_series(0, 360, 90) AS t(a)) AS t(x,y); 624 x | y | atan2d | atan2d_exact 625-----+-----+--------+-------------- 626 10 | 0 | 0 | t 627 0 | 10 | 90 | t 628 -10 | 0 | 180 | t 629 0 | -10 | -90 | t 630 10 | 0 | 0 | t 631(5 rows) 632 633RESET extra_float_digits; 634