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 ('10e40');
12
ERROR: type "real" value out of range: overflow
13
INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e40');
14
ERROR: type "real" value out of range: overflow
15
INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-40');
16
ERROR: type "real" value out of range: underflow
17
INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-40');
18
ERROR: type "real" 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 ('xyz');
23
ERROR: invalid input syntax for type real: "xyz"
24
INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0');
25
ERROR: invalid input syntax for type real: "5.0.0"
26
INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0');
27
ERROR: invalid input syntax for type real: "5 . 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 (' - 3.0');
31
ERROR: invalid input syntax for type real: " - 3.0"
32
INSERT INTO FLOAT4_TBL(f1) VALUES ('123 5');
33
ERROR: invalid input syntax for type real: "123 5"
47
SELECT ' NAN '::float4;
53
SELECT 'infinity'::float4;
59
SELECT ' -INFINiTY '::float4;
66
SELECT 'N A N'::float4;
67
ERROR: invalid input syntax for type real: "N A N"
68
SELECT 'NaN x'::float4;
69
ERROR: invalid input syntax for type real: "NaN x"
70
SELECT ' INFINITY x'::float4;
71
ERROR: invalid input syntax for type real: " INFINITY x"
72
SELECT 'Infinity'::float4 + 100.0;
73
ERROR: type "double precision" value out of range: overflow
74
SELECT 'Infinity'::float4 / 'Infinity'::float4;
80
SELECT 'nan'::float4 / 'nan'::float4;
86
SELECT '' AS five, FLOAT4_TBL.*;
96
SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3';
105
SELECT '' AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3';
111
SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1;
113
-------+-------------
119
SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE f.f1 < '1004.3';
121
-------+-------------
127
SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1;
136
SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <= '1004.3';
145
SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f
148
-------+-------------+--------------
150
| 1.23457e+20 | -1.23457e+21
151
| 1.23457e-20 | -1.23457e-19
154
SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f
157
-------+-------------+-------------
159
| 1.23457e+20 | 1.23457e+20
163
SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f
166
-------+-------------+--------------
168
| 1.23457e+20 | -1.23457e+19
169
| 1.23457e-20 | -1.23457e-21
172
SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f
175
-------+-------------+-------------
177
| 1.23457e+20 | 1.23457e+20
181
-- test divide by zero
182
SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f;
183
ERROR: division by zero
184
SELECT '' AS five, FLOAT4_TBL.*;
194
-- test the unary float4abs operator
195
SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f;
197
------+-------------+-------------
201
| 1.23457e+20 | 1.23457e+20
202
| 1.23457e-20 | 1.23457e-20
206
SET f1 = FLOAT4_TBL.f1 * '-1'
207
WHERE FLOAT4_TBL.f1 > '0.0';
208
SELECT '' AS five, FLOAT4_TBL.*;
210
------+--------------