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 347-- take exp of ln(f.f1) 348SELECT '' AS three, f.f1, exp(ln(f.f1)) AS exp_ln_f1 349 FROM FLOAT8_TBL f 350 WHERE f.f1 > '0.0'; 351 three | f1 | exp_ln_f1 352-------+----------------------+----------------------- 353 | 1004.3 | 1004.3 354 | 1.2345678901234e+200 | 1.23456789012338e+200 355 | 1.2345678901234e-200 | 1.23456789012339e-200 356(3 rows) 357 358-- cube root 359SELECT ||/ float8 '27' AS three; 360 three 361------- 362 3 363(1 row) 364 365SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f; 366 five | f1 | cbrt_f1 367------+----------------------+---------------------- 368 | 0 | 0 369 | 1004.3 | 10.014312837827 370 | -34.84 | -3.26607421344208 371 | 1.2345678901234e+200 | 4.97933859234765e+66 372 | 1.2345678901234e-200 | 2.3112042409018e-67 373(5 rows) 374 375SELECT '' AS five, * FROM FLOAT8_TBL; 376 five | f1 377------+---------------------- 378 | 0 379 | 1004.3 380 | -34.84 381 | 1.2345678901234e+200 382 | 1.2345678901234e-200 383(5 rows) 384 385UPDATE FLOAT8_TBL 386 SET f1 = FLOAT8_TBL.f1 * '-1' 387 WHERE FLOAT8_TBL.f1 > '0.0'; 388SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; 389ERROR: value out of range: overflow 390SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; 391ERROR: value out of range: overflow 392SELECT 0 ^ 0 + 0 ^ 1 + 0 ^ 0.0 + 0 ^ 0.5; 393 ?column? 394---------- 395 2 396(1 row) 397 398SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ; 399ERROR: cannot take logarithm of zero 400SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 < '0.0' ; 401ERROR: cannot take logarithm of a negative number 402SELECT '' AS bad, exp(f.f1) from FLOAT8_TBL f; 403ERROR: value out of range: underflow 404SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f; 405ERROR: division by zero 406SELECT '' AS five, * FROM FLOAT8_TBL; 407 five | f1 408------+----------------------- 409 | 0 410 | -34.84 411 | -1004.3 412 | -1.2345678901234e+200 413 | -1.2345678901234e-200 414(5 rows) 415 416-- test for over- and underflow 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 ('-10e400'); 422ERROR: "-10e400" is out of range for type double precision 423LINE 1: INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400'); 424 ^ 425INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400'); 426INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400'); 427-- maintain external table consistency across platforms 428-- delete all values and reinsert well-behaved ones 429DELETE FROM FLOAT8_TBL; 430INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0'); 431INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84'); 432INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30'); 433INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200'); 434INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200'); 435SELECT '' AS five, * FROM FLOAT8_TBL; 436 five | f1 437------+----------------------- 438 | 0 439 | -34.84 440 | -1004.3 441 | -1.2345678901234e+200 442 | -1.2345678901234e-200 443(5 rows) 444 445-- test edge-case coercions to integer 446SELECT '32767.4'::float8::int2; 447 int2 448------- 449 32767 450(1 row) 451 452SELECT '32767.6'::float8::int2; 453ERROR: smallint out of range 454SELECT '-32768.4'::float8::int2; 455 int2 456-------- 457 -32768 458(1 row) 459 460SELECT '-32768.6'::float8::int2; 461ERROR: smallint out of range 462SELECT '2147483647.4'::float8::int4; 463 int4 464------------ 465 2147483647 466(1 row) 467 468SELECT '2147483647.6'::float8::int4; 469ERROR: integer out of range 470SELECT '-2147483648.4'::float8::int4; 471 int4 472------------- 473 -2147483648 474(1 row) 475 476SELECT '-2147483648.6'::float8::int4; 477ERROR: integer out of range 478SELECT '9223372036854773760'::float8::int8; 479 int8 480--------------------- 481 9223372036854773760 482(1 row) 483 484SELECT '9223372036854775807'::float8::int8; 485ERROR: bigint out of range 486SELECT '-9223372036854775808.5'::float8::int8; 487 int8 488---------------------- 489 -9223372036854775808 490(1 row) 491 492SELECT '-9223372036854780000'::float8::int8; 493ERROR: bigint out of range 494-- test exact cases for trigonometric functions in degrees 495SET extra_float_digits = 3; 496SELECT x, 497 sind(x), 498 sind(x) IN (-1,-0.5,0,0.5,1) AS sind_exact 499FROM (VALUES (0), (30), (90), (150), (180), 500 (210), (270), (330), (360)) AS t(x); 501 x | sind | sind_exact 502-----+------+------------ 503 0 | 0 | t 504 30 | 0.5 | t 505 90 | 1 | t 506 150 | 0.5 | t 507 180 | 0 | t 508 210 | -0.5 | t 509 270 | -1 | t 510 330 | -0.5 | t 511 360 | 0 | t 512(9 rows) 513 514SELECT x, 515 cosd(x), 516 cosd(x) IN (-1,-0.5,0,0.5,1) AS cosd_exact 517FROM (VALUES (0), (60), (90), (120), (180), 518 (240), (270), (300), (360)) AS t(x); 519 x | cosd | cosd_exact 520-----+------+------------ 521 0 | 1 | t 522 60 | 0.5 | t 523 90 | 0 | t 524 120 | -0.5 | t 525 180 | -1 | t 526 240 | -0.5 | t 527 270 | 0 | t 528 300 | 0.5 | t 529 360 | 1 | t 530(9 rows) 531 532SELECT x, 533 tand(x), 534 tand(x) IN ('-Infinity'::float8,-1,0, 535 1,'Infinity'::float8) AS tand_exact, 536 cotd(x), 537 cotd(x) IN ('-Infinity'::float8,-1,0, 538 1,'Infinity'::float8) AS cotd_exact 539FROM (VALUES (0), (45), (90), (135), (180), 540 (225), (270), (315), (360)) AS t(x); 541 x | tand | tand_exact | cotd | cotd_exact 542-----+-----------+------------+-----------+------------ 543 0 | 0 | t | Infinity | t 544 45 | 1 | t | 1 | t 545 90 | Infinity | t | 0 | t 546 135 | -1 | t | -1 | t 547 180 | 0 | t | -Infinity | t 548 225 | 1 | t | 1 | t 549 270 | -Infinity | t | 0 | t 550 315 | -1 | t | -1 | t 551 360 | 0 | t | Infinity | t 552(9 rows) 553 554SELECT x, 555 asind(x), 556 asind(x) IN (-90,-30,0,30,90) AS asind_exact, 557 acosd(x), 558 acosd(x) IN (0,60,90,120,180) AS acosd_exact 559FROM (VALUES (-1), (-0.5), (0), (0.5), (1)) AS t(x); 560 x | asind | asind_exact | acosd | acosd_exact 561------+-------+-------------+-------+------------- 562 -1 | -90 | t | 180 | t 563 -0.5 | -30 | t | 120 | t 564 0 | 0 | t | 90 | t 565 0.5 | 30 | t | 60 | t 566 1 | 90 | t | 0 | t 567(5 rows) 568 569SELECT x, 570 atand(x), 571 atand(x) IN (-90,-45,0,45,90) AS atand_exact 572FROM (VALUES ('-Infinity'::float8), (-1), (0), (1), 573 ('Infinity'::float8)) AS t(x); 574 x | atand | atand_exact 575-----------+-------+------------- 576 -Infinity | -90 | t 577 -1 | -45 | t 578 0 | 0 | t 579 1 | 45 | t 580 Infinity | 90 | t 581(5 rows) 582 583SELECT x, y, 584 atan2d(y, x), 585 atan2d(y, x) IN (-90,0,90,180) AS atan2d_exact 586FROM (SELECT 10*cosd(a), 10*sind(a) 587 FROM generate_series(0, 360, 90) AS t(a)) AS t(x,y); 588 x | y | atan2d | atan2d_exact 589-----+-----+--------+-------------- 590 10 | 0 | 0 | t 591 0 | 10 | 90 | t 592 -10 | 0 | 180 | t 593 0 | -10 | -90 | t 594 10 | 0 | 0 | t 595(5 rows) 596 597RESET extra_float_digits; 598