-- -- FLOAT4 -- CREATE TABLE FLOAT4_TBL (f1 float4); INSERT INTO FLOAT4_TBL(f1) VALUES (' 0.0'); INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30 '); INSERT INTO FLOAT4_TBL(f1) VALUES (' -34.84 '); INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20'); INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20'); -- test for over and under flow INSERT INTO FLOAT4_TBL(f1) VALUES ('10e70'); ERROR: "10e70" is out of range for type real LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('10e70'); ^ INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e70'); ERROR: "-10e70" is out of range for type real LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e70'); ^ INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-70'); ERROR: "10e-70" is out of range for type real LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-70'); ^ INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-70'); ERROR: "-10e-70" is out of range for type real LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-70'); ^ INSERT INTO FLOAT4_TBL(f1) VALUES ('10e70'::float8); ERROR: value out of range: overflow INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e70'::float8); ERROR: value out of range: overflow INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-70'::float8); ERROR: value out of range: underflow INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-70'::float8); ERROR: value out of range: underflow INSERT INTO FLOAT4_TBL(f1) VALUES ('10e400'); ERROR: "10e400" is out of range for type real LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('10e400'); ^ INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e400'); ERROR: "-10e400" is out of range for type real LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e400'); ^ INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-400'); ERROR: "10e-400" is out of range for type real LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-400'); ^ INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-400'); ERROR: "-10e-400" is out of range for type real LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-400'); ^ -- bad input INSERT INTO FLOAT4_TBL(f1) VALUES (''); ERROR: invalid input syntax for type real: "" LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES (''); ^ INSERT INTO FLOAT4_TBL(f1) VALUES (' '); ERROR: invalid input syntax for type real: " " LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES (' '); ^ INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz'); ERROR: invalid input syntax for type real: "xyz" LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz'); ^ INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0'); ERROR: invalid input syntax for type real: "5.0.0" LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0'); ^ INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0'); ERROR: invalid input syntax for type real: "5 . 0" LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0'); ^ INSERT INTO FLOAT4_TBL(f1) VALUES ('5. 0'); ERROR: invalid input syntax for type real: "5. 0" LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('5. 0'); ^ INSERT INTO FLOAT4_TBL(f1) VALUES (' - 3.0'); ERROR: invalid input syntax for type real: " - 3.0" LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES (' - 3.0'); ^ INSERT INTO FLOAT4_TBL(f1) VALUES ('123 5'); ERROR: invalid input syntax for type real: "123 5" LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('123 5'); ^ -- special inputs SELECT 'NaN'::float4; float4 -------- NaN (1 row) SELECT 'nan'::float4; float4 -------- NaN (1 row) SELECT ' NAN '::float4; float4 -------- NaN (1 row) SELECT 'infinity'::float4; float4 ---------- Infinity (1 row) SELECT ' -INFINiTY '::float4; float4 ----------- -Infinity (1 row) -- bad special inputs SELECT 'N A N'::float4; ERROR: invalid input syntax for type real: "N A N" LINE 1: SELECT 'N A N'::float4; ^ SELECT 'NaN x'::float4; ERROR: invalid input syntax for type real: "NaN x" LINE 1: SELECT 'NaN x'::float4; ^ SELECT ' INFINITY x'::float4; ERROR: invalid input syntax for type real: " INFINITY x" LINE 1: SELECT ' INFINITY x'::float4; ^ SELECT 'Infinity'::float4 + 100.0; ?column? ---------- Infinity (1 row) SELECT 'Infinity'::float4 / 'Infinity'::float4; ?column? ---------- NaN (1 row) SELECT '42'::float4 / 'Infinity'::float4; ?column? ---------- 0 (1 row) SELECT 'nan'::float4 / 'nan'::float4; ?column? ---------- NaN (1 row) SELECT 'nan'::float4 / '0'::float4; ?column? ---------- NaN (1 row) SELECT 'nan'::numeric::float4; float4 -------- NaN (1 row) SELECT * FROM FLOAT4_TBL; f1 --------------- 0 1004.3 -34.84 1.2345679e+20 1.2345679e-20 (5 rows) SELECT f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3'; f1 --------------- 0 -34.84 1.2345679e+20 1.2345679e-20 (4 rows) SELECT f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3'; f1 -------- 1004.3 (1 row) SELECT f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1; f1 --------------- 0 -34.84 1.2345679e-20 (3 rows) SELECT f.* FROM FLOAT4_TBL f WHERE f.f1 < '1004.3'; f1 --------------- 0 -34.84 1.2345679e-20 (3 rows) SELECT f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1; f1 --------------- 0 1004.3 -34.84 1.2345679e-20 (4 rows) SELECT f.* FROM FLOAT4_TBL f WHERE f.f1 <= '1004.3'; f1 --------------- 0 1004.3 -34.84 1.2345679e-20 (4 rows) SELECT f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f WHERE f.f1 > '0.0'; f1 | x ---------------+---------------- 1004.3 | -10043 1.2345679e+20 | -1.2345678e+21 1.2345679e-20 | -1.2345678e-19 (3 rows) SELECT f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f WHERE f.f1 > '0.0'; f1 | x ---------------+---------------- 1004.3 | -100.43 1.2345679e+20 | -1.2345679e+19 1.2345679e-20 | -1.2345679e-21 (3 rows) SELECT f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f WHERE f.f1 > '0.0'; f1 | x ---------------+--------------- 1004.3 | 1014.3 1.2345679e+20 | 1.2345679e+20 1.2345679e-20 | 10 (3 rows) -- test divide by zero SELECT f.f1 / '0.0' from FLOAT4_TBL f; ERROR: division by zero SELECT * FROM FLOAT4_TBL; f1 --------------- 0 1004.3 -34.84 1.2345679e+20 1.2345679e-20 (5 rows) -- test the unary float4abs operator SELECT f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f; f1 | abs_f1 ---------------+--------------- 0 | 0 1004.3 | 1004.3 -34.84 | 34.84 1.2345679e+20 | 1.2345679e+20 1.2345679e-20 | 1.2345679e-20 (5 rows) -- test edge-case coercions to integer SELECT '32767.4'::float4::int2; int2 ------- 32767 (1 row) SELECT '32767.6'::float4::int2; ERROR: smallint out of range SELECT '-32768.4'::float4::int2; int2 -------- -32768 (1 row) SELECT '-32768.6'::float4::int2; ERROR: smallint out of range SELECT '2147483520'::float4::int4; int4 ------------ 2147483520 (1 row) SELECT '2147483647'::float4::int4; ERROR: integer out of range SELECT '-2147483648.5'::float4::int4; int4 ------------- -2147483648 (1 row) SELECT '-2147483900'::float4::int4; ERROR: integer out of range SELECT '9223369837831520256'::float4::int8; int8 --------------------- 9223369837831520256 (1 row) SELECT '9223372036854775807'::float4::int8; ERROR: bigint out of range SELECT '-9223372036854775808.5'::float4::int8; int8 ---------------------- -9223372036854775808 (1 row) SELECT '-9223380000000000000'::float4::int8; ERROR: bigint out of range -- Test for correct input rounding in edge cases. -- These lists are from Paxson 1991, excluding subnormals and -- inputs of over 9 sig. digits. SELECT float4send('5e-20'::float4); float4send ------------ \x1f6c1e4a (1 row) SELECT float4send('67e14'::float4); float4send ------------ \x59be6cea (1 row) SELECT float4send('985e15'::float4); float4send ------------ \x5d5ab6c4 (1 row) SELECT float4send('55895e-16'::float4); float4send ------------ \x2cc4a9bd (1 row) SELECT float4send('7038531e-32'::float4); float4send ------------ \x15ae43fd (1 row) SELECT float4send('702990899e-20'::float4); float4send ------------ \x2cf757ca (1 row) SELECT float4send('3e-23'::float4); float4send ------------ \x1a111234 (1 row) SELECT float4send('57e18'::float4); float4send ------------ \x6045c22c (1 row) SELECT float4send('789e-35'::float4); float4send ------------ \x0a23de70 (1 row) SELECT float4send('2539e-18'::float4); float4send ------------ \x2736f449 (1 row) SELECT float4send('76173e28'::float4); float4send ------------ \x7616398a (1 row) SELECT float4send('887745e-11'::float4); float4send ------------ \x3714f05c (1 row) SELECT float4send('5382571e-37'::float4); float4send ------------ \x0d2eaca7 (1 row) SELECT float4send('82381273e-35'::float4); float4send ------------ \x128289d1 (1 row) SELECT float4send('750486563e-38'::float4); float4send ------------ \x0f18377e (1 row) -- Test that the smallest possible normalized input value inputs -- correctly, either in 9-significant-digit or shortest-decimal -- format. -- -- exact val is 1.1754943508... -- shortest val is 1.1754944000 -- midpoint to next val is 1.1754944208... SELECT float4send('1.17549435e-38'::float4); float4send ------------ \x00800000 (1 row) SELECT float4send('1.1754944e-38'::float4); float4send ------------ \x00800000 (1 row)