4
CREATE TABLE FLOAT8_TBL(f1 float8);
5
INSERT INTO FLOAT8_TBL(f1) VALUES (' 0.0 ');
6
INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30 ');
7
INSERT INTO FLOAT8_TBL(f1) VALUES (' -34.84');
8
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
9
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
10
-- test for underflow and overflow handling
11
SELECT '10e400'::float8;
12
ERROR: "10e400" is out of range for type double precision
13
SELECT '-10e400'::float8;
14
ERROR: "-10e400" is out of range for type double precision
15
SELECT '10e-400'::float8;
21
SELECT '-10e-400'::float8;
28
INSERT INTO FLOAT8_TBL(f1) VALUES (' ');
29
ERROR: invalid input syntax for type double precision: " "
30
INSERT INTO FLOAT8_TBL(f1) VALUES ('xyz');
31
ERROR: invalid input syntax for type double precision: "xyz"
32
INSERT INTO FLOAT8_TBL(f1) VALUES ('5.0.0');
33
ERROR: invalid input syntax for type double precision: "5.0.0"
34
INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0');
35
ERROR: invalid input syntax for type double precision: "5 . 0"
36
INSERT INTO FLOAT8_TBL(f1) VALUES ('5. 0');
37
ERROR: invalid input syntax for type double precision: "5. 0"
38
INSERT INTO FLOAT8_TBL(f1) VALUES (' - 3');
39
ERROR: invalid input syntax for type double precision: " - 3"
40
INSERT INTO FLOAT8_TBL(f1) VALUES ('123 5');
41
ERROR: invalid input syntax for type double precision: "123 5"
55
SELECT ' NAN '::float8;
61
SELECT 'infinity'::float8;
67
SELECT ' -INFINiTY '::float8;
74
SELECT 'N A N'::float8;
75
ERROR: invalid input syntax for type double precision: "N A N"
76
SELECT 'NaN x'::float8;
77
ERROR: invalid input syntax for type double precision: "NaN x"
78
SELECT ' INFINITY x'::float8;
79
ERROR: invalid input syntax for type double precision: " INFINITY x"
80
SELECT 'Infinity'::float8 + 100.0;
81
ERROR: type "double precision" value out of range: overflow
82
SELECT 'Infinity'::float8 / 'Infinity'::float8;
88
SELECT 'nan'::float8 / 'nan'::float8;
94
SELECT '' AS five, FLOAT8_TBL.*;
96
------+----------------------
100
| 1.2345678901234e+200
101
| 1.2345678901234e-200
104
SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3';
106
------+----------------------
109
| 1.2345678901234e+200
110
| 1.2345678901234e-200
113
SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3';
119
SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE '1004.3' > f.f1;
121
-------+----------------------
124
| 1.2345678901234e-200
127
SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE f.f1 < '1004.3';
129
-------+----------------------
132
| 1.2345678901234e-200
135
SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1;
137
------+----------------------
141
| 1.2345678901234e-200
144
SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <= '1004.3';
146
------+----------------------
150
| 1.2345678901234e-200
153
SELECT '' AS three, f.f1, f.f1 * '-10' AS x
157
-------+----------------------+-----------------------
159
| 1.2345678901234e+200 | -1.2345678901234e+201
160
| 1.2345678901234e-200 | -1.2345678901234e-199
163
SELECT '' AS three, f.f1, f.f1 + '-10' AS x
167
-------+----------------------+----------------------
169
| 1.2345678901234e+200 | 1.2345678901234e+200
170
| 1.2345678901234e-200 | -10
173
SELECT '' AS three, f.f1, f.f1 / '-10' AS x
177
-------+----------------------+-----------------------
179
| 1.2345678901234e+200 | -1.2345678901234e+199
180
| 1.2345678901234e-200 | -1.2345678901234e-201
183
SELECT '' AS three, f.f1, f.f1 - '-10' AS x
187
-------+----------------------+----------------------
189
| 1.2345678901234e+200 | 1.2345678901234e+200
190
| 1.2345678901234e-200 | 10
193
SELECT '' AS one, f.f1 ^ '2.0' AS square_f1
194
FROM FLOAT8_TBL f where f.f1 = '1004.3';
201
SELECT '' AS five, f.f1, @f.f1 AS abs_f1
204
------+----------------------+----------------------
208
| 1.2345678901234e+200 | 1.2345678901234e+200
209
| 1.2345678901234e-200 | 1.2345678901234e-200
213
SELECT '' AS five, f.f1, %f.f1 AS trunc_f1
216
------+----------------------+----------------------
220
| 1.2345678901234e+200 | 1.2345678901234e+200
221
| 1.2345678901234e-200 | 0
225
SELECT '' AS five, f.f1, f.f1 % AS round_f1
228
------+----------------------+----------------------
232
| 1.2345678901234e+200 | 1.2345678901234e+200
233
| 1.2345678901234e-200 | 0
237
select ceil(f1) as ceil_f1 from float8_tbl f;
239
----------------------
247
select ceiling(f1) as ceiling_f1 from float8_tbl f;
249
----------------------
258
select floor(f1) as floor_f1 from float8_tbl f;
260
----------------------
269
select sign(f1) as sign_f1 from float8_tbl f;
280
SELECT sqrt(float8 '64') AS eight;
286
SELECT |/ float8 '64' AS eight;
292
SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1
296
-------+----------------------+-----------------------
297
| 1004.3 | 31.6906926399535
298
| 1.2345678901234e+200 | 1.11111110611109e+100
299
| 1.2345678901234e-200 | 1.11111110611109e-100
303
SELECT power(float8 '144', float8 '0.5');
309
-- take exp of ln(f.f1)
310
SELECT '' AS three, f.f1, exp(ln(f.f1)) AS exp_ln_f1
313
three | f1 | exp_ln_f1
314
-------+----------------------+-----------------------
316
| 1.2345678901234e+200 | 1.23456789012338e+200
317
| 1.2345678901234e-200 | 1.23456789012339e-200
321
SELECT ||/ float8 '27' AS three;
327
SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f;
329
------+----------------------+----------------------
331
| 1004.3 | 10.014312837827
332
| -34.84 | -3.26607421344208
333
| 1.2345678901234e+200 | 4.97933859234765e+66
334
| 1.2345678901234e-200 | 2.3112042409018e-67
337
SELECT '' AS five, FLOAT8_TBL.*;
339
------+----------------------
343
| 1.2345678901234e+200
344
| 1.2345678901234e-200
348
SET f1 = FLOAT8_TBL.f1 * '-1'
349
WHERE FLOAT8_TBL.f1 > '0.0';
350
SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f;
351
ERROR: type "double precision" value out of range: overflow
352
SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f;
353
ERROR: result is out of range
354
SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ;
355
ERROR: cannot take logarithm of zero
356
SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 < '0.0' ;
357
ERROR: cannot take logarithm of a negative number
358
SELECT '' AS bad, exp(f.f1) from FLOAT8_TBL f;
359
ERROR: result is out of range
360
SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f;
361
ERROR: division by zero
362
SELECT '' AS five, FLOAT8_TBL.*;
364
------+-----------------------
368
| -1.2345678901234e+200
369
| -1.2345678901234e-200
372
-- test for over- and underflow
373
INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
374
ERROR: "10e400" is out of range for type double precision
375
INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
376
ERROR: "-10e400" is out of range for type double precision
377
INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
378
INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
379
-- maintain external table consistency across platforms
380
-- delete all values and reinsert well-behaved ones
381
DELETE FROM FLOAT8_TBL;
382
INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
383
INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
384
INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30');
385
INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200');
386
INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200');
387
SELECT '' AS five, FLOAT8_TBL.*;
389
------+-----------------------
393
| -1.2345678901234e+200
394
| -1.2345678901234e-200