1-- 2-- FLOAT4 3-- 4CREATE TABLE FLOAT4_TBL (f1 float4); 5INSERT INTO FLOAT4_TBL(f1) VALUES (' 0.0'); 6INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30 '); 7INSERT INTO FLOAT4_TBL(f1) VALUES (' -34.84 '); 8INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20'); 9INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20'); 10-- test for over and under flow 11INSERT INTO FLOAT4_TBL(f1) VALUES ('10e70'); 12ERROR: value out of range: overflow 13LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('10e70'); 14 ^ 15INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e70'); 16ERROR: value out of range: overflow 17LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e70'); 18 ^ 19INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-70'); 20ERROR: value out of range: underflow 21LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-70'); 22 ^ 23INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-70'); 24ERROR: value out of range: underflow 25LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-70'); 26 ^ 27-- bad input 28INSERT INTO FLOAT4_TBL(f1) VALUES (''); 29ERROR: invalid input syntax for type real: "" 30LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES (''); 31 ^ 32INSERT INTO FLOAT4_TBL(f1) VALUES (' '); 33ERROR: invalid input syntax for type real: " " 34LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES (' '); 35 ^ 36INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz'); 37ERROR: invalid input syntax for type real: "xyz" 38LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz'); 39 ^ 40INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0'); 41ERROR: invalid input syntax for type real: "5.0.0" 42LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0'); 43 ^ 44INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0'); 45ERROR: invalid input syntax for type real: "5 . 0" 46LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0'); 47 ^ 48INSERT INTO FLOAT4_TBL(f1) VALUES ('5. 0'); 49ERROR: invalid input syntax for type real: "5. 0" 50LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('5. 0'); 51 ^ 52INSERT INTO FLOAT4_TBL(f1) VALUES (' - 3.0'); 53ERROR: invalid input syntax for type real: " - 3.0" 54LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES (' - 3.0'); 55 ^ 56INSERT INTO FLOAT4_TBL(f1) VALUES ('123 5'); 57ERROR: invalid input syntax for type real: "123 5" 58LINE 1: INSERT INTO FLOAT4_TBL(f1) VALUES ('123 5'); 59 ^ 60-- special inputs 61SELECT 'NaN'::float4; 62 float4 63-------- 64 NaN 65(1 row) 66 67SELECT 'nan'::float4; 68 float4 69-------- 70 NaN 71(1 row) 72 73SELECT ' NAN '::float4; 74 float4 75-------- 76 NaN 77(1 row) 78 79SELECT 'infinity'::float4; 80 float4 81---------- 82 Infinity 83(1 row) 84 85SELECT ' -INFINiTY '::float4; 86 float4 87----------- 88 -Infinity 89(1 row) 90 91-- bad special inputs 92SELECT 'N A N'::float4; 93ERROR: invalid input syntax for type real: "N A N" 94LINE 1: SELECT 'N A N'::float4; 95 ^ 96SELECT 'NaN x'::float4; 97ERROR: invalid input syntax for type real: "NaN x" 98LINE 1: SELECT 'NaN x'::float4; 99 ^ 100SELECT ' INFINITY x'::float4; 101ERROR: invalid input syntax for type real: " INFINITY x" 102LINE 1: SELECT ' INFINITY x'::float4; 103 ^ 104SELECT 'Infinity'::float4 + 100.0; 105 ?column? 106---------- 107 Infinity 108(1 row) 109 110SELECT 'Infinity'::float4 / 'Infinity'::float4; 111 ?column? 112---------- 113 NaN 114(1 row) 115 116SELECT 'nan'::float4 / 'nan'::float4; 117 ?column? 118---------- 119 NaN 120(1 row) 121 122SELECT 'nan'::numeric::float4; 123 float4 124-------- 125 NaN 126(1 row) 127 128SELECT '' AS five, * FROM FLOAT4_TBL; 129 five | f1 130------+-------------- 131 | 0 132 | 1004.3 133 | -34.84 134 | 1.23457e+020 135 | 1.23457e-020 136(5 rows) 137 138SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3'; 139 four | f1 140------+-------------- 141 | 0 142 | -34.84 143 | 1.23457e+020 144 | 1.23457e-020 145(4 rows) 146 147SELECT '' AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3'; 148 one | f1 149-----+-------- 150 | 1004.3 151(1 row) 152 153SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1; 154 three | f1 155-------+-------------- 156 | 0 157 | -34.84 158 | 1.23457e-020 159(3 rows) 160 161SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE f.f1 < '1004.3'; 162 three | f1 163-------+-------------- 164 | 0 165 | -34.84 166 | 1.23457e-020 167(3 rows) 168 169SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1; 170 four | f1 171------+-------------- 172 | 0 173 | 1004.3 174 | -34.84 175 | 1.23457e-020 176(4 rows) 177 178SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <= '1004.3'; 179 four | f1 180------+-------------- 181 | 0 182 | 1004.3 183 | -34.84 184 | 1.23457e-020 185(4 rows) 186 187SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f 188 WHERE f.f1 > '0.0'; 189 three | f1 | x 190-------+--------------+--------------- 191 | 1004.3 | -10043 192 | 1.23457e+020 | -1.23457e+021 193 | 1.23457e-020 | -1.23457e-019 194(3 rows) 195 196SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f 197 WHERE f.f1 > '0.0'; 198 three | f1 | x 199-------+--------------+-------------- 200 | 1004.3 | 994.3 201 | 1.23457e+020 | 1.23457e+020 202 | 1.23457e-020 | -10 203(3 rows) 204 205SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f 206 WHERE f.f1 > '0.0'; 207 three | f1 | x 208-------+--------------+--------------- 209 | 1004.3 | -100.43 210 | 1.23457e+020 | -1.23457e+019 211 | 1.23457e-020 | -1.23457e-021 212(3 rows) 213 214SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f 215 WHERE f.f1 > '0.0'; 216 three | f1 | x 217-------+--------------+-------------- 218 | 1004.3 | 1014.3 219 | 1.23457e+020 | 1.23457e+020 220 | 1.23457e-020 | 10 221(3 rows) 222 223-- test divide by zero 224SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f; 225ERROR: division by zero 226SELECT '' AS five, * FROM FLOAT4_TBL; 227 five | f1 228------+-------------- 229 | 0 230 | 1004.3 231 | -34.84 232 | 1.23457e+020 233 | 1.23457e-020 234(5 rows) 235 236-- test the unary float4abs operator 237SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f; 238 five | f1 | abs_f1 239------+--------------+-------------- 240 | 0 | 0 241 | 1004.3 | 1004.3 242 | -34.84 | 34.84 243 | 1.23457e+020 | 1.23457e+020 244 | 1.23457e-020 | 1.23457e-020 245(5 rows) 246 247UPDATE FLOAT4_TBL 248 SET f1 = FLOAT4_TBL.f1 * '-1' 249 WHERE FLOAT4_TBL.f1 > '0.0'; 250SELECT '' AS five, * FROM FLOAT4_TBL; 251 five | f1 252------+--------------- 253 | 0 254 | -34.84 255 | -1004.3 256 | -1.23457e+020 257 | -1.23457e-020 258(5 rows) 259 260-- test edge-case coercions to integer 261SELECT '32767.4'::float4::int2; 262 int2 263------- 264 32767 265(1 row) 266 267SELECT '32767.6'::float4::int2; 268ERROR: smallint out of range 269SELECT '-32768.4'::float4::int2; 270 int2 271-------- 272 -32768 273(1 row) 274 275SELECT '-32768.6'::float4::int2; 276ERROR: smallint out of range 277SELECT '2147483520'::float4::int4; 278 int4 279------------ 280 2147483520 281(1 row) 282 283SELECT '2147483647'::float4::int4; 284ERROR: integer out of range 285SELECT '-2147483648.5'::float4::int4; 286 int4 287------------- 288 -2147483648 289(1 row) 290 291SELECT '-2147483900'::float4::int4; 292ERROR: integer out of range 293SELECT '9223369837831520256'::float4::int8; 294 int8 295--------------------- 296 9223369837831520256 297(1 row) 298 299SELECT '9223372036854775807'::float4::int8; 300ERROR: bigint out of range 301SELECT '-9223372036854775808.5'::float4::int8; 302 int8 303---------------------- 304 -9223372036854775808 305(1 row) 306 307SELECT '-9223380000000000000'::float4::int8; 308ERROR: bigint out of range 309