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;
16
ERROR: "10e-400" is out of range for type double precision
17
SELECT '-10e-400'::float8;
18
ERROR: "-10e-400" is out of range for type double precision
20
INSERT INTO FLOAT8_TBL(f1) VALUES (' ');
21
ERROR: invalid input syntax for type double precision: " "
22
INSERT INTO FLOAT8_TBL(f1) VALUES ('xyz');
23
ERROR: invalid input syntax for type double precision: "xyz"
24
INSERT INTO FLOAT8_TBL(f1) VALUES ('5.0.0');
25
ERROR: invalid input syntax for type double precision: "5.0.0"
26
INSERT INTO FLOAT8_TBL(f1) VALUES ('5 . 0');
27
ERROR: invalid input syntax for type double precision: "5 . 0"
28
INSERT INTO FLOAT8_TBL(f1) VALUES ('5. 0');
29
ERROR: invalid input syntax for type double precision: "5. 0"
30
INSERT INTO FLOAT8_TBL(f1) VALUES (' - 3');
31
ERROR: invalid input syntax for type double precision: " - 3"
32
INSERT INTO FLOAT8_TBL(f1) VALUES ('123 5');
33
ERROR: invalid input syntax for type double precision: "123 5"
47
SELECT ' NAN '::float8;
53
SELECT 'infinity'::float8;
59
SELECT ' -INFINiTY '::float8;
66
SELECT 'N A N'::float8;
67
ERROR: invalid input syntax for type double precision: "N A N"
68
SELECT 'NaN x'::float8;
69
ERROR: invalid input syntax for type double precision: "NaN x"
70
SELECT ' INFINITY x'::float8;
71
ERROR: invalid input syntax for type double precision: " INFINITY x"
72
SELECT 'Infinity'::float8 + 100.0;
73
ERROR: type "double precision" value out of range: overflow
74
SELECT 'Infinity'::float8 / 'Infinity'::float8;
80
SELECT 'nan'::float8 / 'nan'::float8;
86
SELECT '' AS five, FLOAT8_TBL.*;
88
------+----------------------
92
| 1.2345678901234e+200
93
| 1.2345678901234e-200
96
SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3';
98
------+----------------------
101
| 1.2345678901234e+200
102
| 1.2345678901234e-200
105
SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3';
111
SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE '1004.3' > f.f1;
113
-------+----------------------
116
| 1.2345678901234e-200
119
SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE f.f1 < '1004.3';
121
-------+----------------------
124
| 1.2345678901234e-200
127
SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1;
129
------+----------------------
133
| 1.2345678901234e-200
136
SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <= '1004.3';
138
------+----------------------
142
| 1.2345678901234e-200
145
SELECT '' AS three, f.f1, f.f1 * '-10' AS x
149
-------+----------------------+-----------------------
151
| 1.2345678901234e+200 | -1.2345678901234e+201
152
| 1.2345678901234e-200 | -1.2345678901234e-199
155
SELECT '' AS three, f.f1, f.f1 + '-10' AS x
159
-------+----------------------+----------------------
161
| 1.2345678901234e+200 | 1.2345678901234e+200
162
| 1.2345678901234e-200 | -10
165
SELECT '' AS three, f.f1, f.f1 / '-10' AS x
169
-------+----------------------+-----------------------
171
| 1.2345678901234e+200 | -1.2345678901234e+199
172
| 1.2345678901234e-200 | -1.2345678901234e-201
175
SELECT '' AS three, f.f1, f.f1 - '-10' AS x
179
-------+----------------------+----------------------
181
| 1.2345678901234e+200 | 1.2345678901234e+200
182
| 1.2345678901234e-200 | 10
185
SELECT '' AS one, f.f1 ^ '2.0' AS square_f1
186
FROM FLOAT8_TBL f where f.f1 = '1004.3';
193
SELECT '' AS five, f.f1, @f.f1 AS abs_f1
196
------+----------------------+----------------------
200
| 1.2345678901234e+200 | 1.2345678901234e+200
201
| 1.2345678901234e-200 | 1.2345678901234e-200
205
SELECT '' AS five, f.f1, %f.f1 AS trunc_f1
208
------+----------------------+----------------------
212
| 1.2345678901234e+200 | 1.2345678901234e+200
213
| 1.2345678901234e-200 | 0
217
SELECT '' AS five, f.f1, f.f1 % AS round_f1
220
------+----------------------+----------------------
224
| 1.2345678901234e+200 | 1.2345678901234e+200
225
| 1.2345678901234e-200 | 0
229
select ceil(f1) as ceil_f1 from float8_tbl f;
231
----------------------
239
select ceiling(f1) as ceiling_f1 from float8_tbl f;
241
----------------------
250
select floor(f1) as floor_f1 from float8_tbl f;
252
----------------------
261
select sign(f1) as sign_f1 from float8_tbl f;
272
SELECT sqrt(float8 '64') AS eight;
278
SELECT |/ float8 '64' AS eight;
284
SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1
288
-------+----------------------+-----------------------
289
| 1004.3 | 31.6906926399535
290
| 1.2345678901234e+200 | 1.11111110611109e+100
291
| 1.2345678901234e-200 | 1.11111110611109e-100
295
SELECT power(float8 '144', float8 '0.5');
301
-- take exp of ln(f.f1)
302
SELECT '' AS three, f.f1, exp(ln(f.f1)) AS exp_ln_f1
305
three | f1 | exp_ln_f1
306
-------+----------------------+-----------------------
308
| 1.2345678901234e+200 | 1.23456789012338e+200
309
| 1.2345678901234e-200 | 1.23456789012339e-200
313
SELECT ||/ float8 '27' AS three;
319
SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f;
321
------+----------------------+-----------------------
323
| 1004.3 | 10.014312837827
324
| -34.84 | -3.26607421344208
325
| 1.2345678901234e+200 | 4.97933859234765e+066
326
| 1.2345678901234e-200 | 2.3112042409018e-067
329
SELECT '' AS five, FLOAT8_TBL.*;
331
------+----------------------
335
| 1.2345678901234e+200
336
| 1.2345678901234e-200
340
SET f1 = FLOAT8_TBL.f1 * '-1'
341
WHERE FLOAT8_TBL.f1 > '0.0';
342
SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f;
343
ERROR: type "double precision" value out of range: overflow
344
SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f;
345
ERROR: result is out of range
346
SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ;
347
ERROR: cannot take logarithm of zero
348
SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 < '0.0' ;
349
ERROR: cannot take logarithm of a negative number
350
SELECT '' AS bad, exp(f.f1) from FLOAT8_TBL f;
351
ERROR: result is out of range
352
SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f;
353
ERROR: division by zero
354
SELECT '' AS five, FLOAT8_TBL.*;
356
------+-----------------------
360
| -1.2345678901234e+200
361
| -1.2345678901234e-200
364
-- test for over- and underflow
365
INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
366
ERROR: "10e400" is out of range for type double precision
367
INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
368
ERROR: "-10e400" is out of range for type double precision
369
INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
370
ERROR: "10e-400" is out of range for type double precision
371
INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
372
ERROR: "-10e-400" is out of range for type double precision
373
-- maintain external table consistency across platforms
374
-- delete all values and reinsert well-behaved ones
375
DELETE FROM FLOAT8_TBL;
376
INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
377
INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
378
INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30');
379
INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200');
380
INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200');
381
SELECT '' AS five, FLOAT8_TBL.*;
383
------+-----------------------
387
| -1.2345678901234e+200
388
| -1.2345678901234e-200