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