4 CREATE TABLE FLOAT4_TBL (f1 float4);
5 INSERT INTO FLOAT4_TBL(f1) VALUES (' 0.0');
6 INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30 ');
7 INSERT INTO FLOAT4_TBL(f1) VALUES (' -34.84 ');
8 INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20');
9 INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20');
10 -- test for over and under flow
11 INSERT INTO FLOAT4_TBL(f1) VALUES ('10e70');
12 ERROR: value out of range: overflow
13 INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e70');
14 ERROR: value out of range: overflow
15 INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-70');
16 ERROR: value out of range: underflow
17 INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-70');
18 ERROR: value out of range: underflow
20 INSERT INTO FLOAT4_TBL(f1) VALUES ('');
21 ERROR: invalid input syntax for type real: ""
22 INSERT INTO FLOAT4_TBL(f1) VALUES (' ');
23 ERROR: invalid input syntax for type real: " "
24 INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz');
25 ERROR: invalid input syntax for type real: "xyz"
26 INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0');
27 ERROR: invalid input syntax for type real: "5.0.0"
28 INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0');
29 ERROR: invalid input syntax for type real: "5 . 0"
30 INSERT INTO FLOAT4_TBL(f1) VALUES ('5. 0');
31 ERROR: invalid input syntax for type real: "5. 0"
32 INSERT INTO FLOAT4_TBL(f1) VALUES (' - 3.0');
33 ERROR: invalid input syntax for type real: " - 3.0"
34 INSERT INTO FLOAT4_TBL(f1) VALUES ('123 5');
35 ERROR: invalid input syntax for type real: "123 5"
49 SELECT ' NAN '::float4;
55 SELECT 'infinity'::float4;
61 SELECT ' -INFINiTY '::float4;
68 SELECT 'N A N'::float4;
69 ERROR: invalid input syntax for type real: "N A N"
70 SELECT 'NaN x'::float4;
71 ERROR: invalid input syntax for type real: "NaN x"
72 SELECT ' INFINITY x'::float4;
73 ERROR: invalid input syntax for type real: " INFINITY x"
74 SELECT 'Infinity'::float4 + 100.0;
80 SELECT 'Infinity'::float4 / 'Infinity'::float4;
86 SELECT 'nan'::float4 / 'nan'::float4;
92 SELECT '' AS five, * FROM FLOAT4_TBL;
102 SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3';
111 SELECT '' AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3';
117 SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1;
119 -------+-------------
125 SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE f.f1 < '1004.3';
127 -------+-------------
133 SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1;
142 SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <= '1004.3';
151 SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f
154 -------+-------------+--------------
156 | 1.23457e+20 | -1.23457e+21
157 | 1.23457e-20 | -1.23457e-19
160 SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f
163 -------+-------------+-------------
165 | 1.23457e+20 | 1.23457e+20
169 SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f
172 -------+-------------+--------------
174 | 1.23457e+20 | -1.23457e+19
175 | 1.23457e-20 | -1.23457e-21
178 SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f
181 -------+-------------+-------------
183 | 1.23457e+20 | 1.23457e+20
187 -- test divide by zero
188 SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f;
189 ERROR: division by zero
190 SELECT '' AS five, * FROM FLOAT4_TBL;
200 -- test the unary float4abs operator
201 SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f;
203 ------+-------------+-------------
207 | 1.23457e+20 | 1.23457e+20
208 | 1.23457e-20 | 1.23457e-20
212 SET f1 = FLOAT4_TBL.f1 * '-1'
213 WHERE FLOAT4_TBL.f1 > '0.0';
214 SELECT '' AS five, * FROM FLOAT4_TBL;
216 ------+--------------