| blob | 0898107ec302b17bff19dba4d8bf7dbe0b119585 |
1 --
2 -- NUMERIC
3 --
4 CREATE TABLE num_data (id int4, val numeric(210,10));
5 CREATE TABLE num_exp_add (id1 int4, id2 int4, expected numeric(210,10));
6 CREATE TABLE num_exp_sub (id1 int4, id2 int4, expected numeric(210,10));
7 CREATE TABLE num_exp_div (id1 int4, id2 int4, expected numeric(210,10));
8 CREATE TABLE num_exp_mul (id1 int4, id2 int4, expected numeric(210,10));
9 CREATE TABLE num_exp_sqrt (id int4, expected numeric(210,10));
10 CREATE TABLE num_exp_ln (id int4, expected numeric(210,10));
11 CREATE TABLE num_exp_log10 (id int4, expected numeric(210,10));
12 CREATE TABLE num_exp_power_10_ln (id int4, expected numeric(210,10));
13 CREATE TABLE num_result (id1 int4, id2 int4, result numeric(210,10));
14 -- ******************************
15 -- * The following EXPECTED results are computed by bc(1)
16 -- * with a scale of 200
17 -- ******************************
18 BEGIN TRANSACTION;
19 INSERT INTO num_exp_add VALUES (0,0,'0');
20 INSERT INTO num_exp_sub VALUES (0,0,'0');
21 INSERT INTO num_exp_mul VALUES (0,0,'0');
22 INSERT INTO num_exp_div VALUES (0,0,'NaN');
23 INSERT INTO num_exp_add VALUES (0,1,'0');
24 INSERT INTO num_exp_sub VALUES (0,1,'0');
25 INSERT INTO num_exp_mul VALUES (0,1,'0');
26 INSERT INTO num_exp_div VALUES (0,1,'NaN');
27 INSERT INTO num_exp_add VALUES (0,2,'-34338492.215397047');
28 INSERT INTO num_exp_sub VALUES (0,2,'34338492.215397047');
29 INSERT INTO num_exp_mul VALUES (0,2,'0');
30 INSERT INTO num_exp_div VALUES (0,2,'0');
31 INSERT INTO num_exp_add VALUES (0,3,'4.31');
32 INSERT INTO num_exp_sub VALUES (0,3,'-4.31');
33 INSERT INTO num_exp_mul VALUES (0,3,'0');
34 INSERT INTO num_exp_div VALUES (0,3,'0');
35 INSERT INTO num_exp_add VALUES (0,4,'7799461.4119');
36 INSERT INTO num_exp_sub VALUES (0,4,'-7799461.4119');
37 INSERT INTO num_exp_mul VALUES (0,4,'0');
38 INSERT INTO num_exp_div VALUES (0,4,'0');
39 INSERT INTO num_exp_add VALUES (0,5,'16397.038491');
40 INSERT INTO num_exp_sub VALUES (0,5,'-16397.038491');
41 INSERT INTO num_exp_mul VALUES (0,5,'0');
42 INSERT INTO num_exp_div VALUES (0,5,'0');
43 INSERT INTO num_exp_add VALUES (0,6,'93901.57763026');
44 INSERT INTO num_exp_sub VALUES (0,6,'-93901.57763026');
45 INSERT INTO num_exp_mul VALUES (0,6,'0');
46 INSERT INTO num_exp_div VALUES (0,6,'0');
47 INSERT INTO num_exp_add VALUES (0,7,'-83028485');
48 INSERT INTO num_exp_sub VALUES (0,7,'83028485');
49 INSERT INTO num_exp_mul VALUES (0,7,'0');
50 INSERT INTO num_exp_div VALUES (0,7,'0');
51 INSERT INTO num_exp_add VALUES (0,8,'74881');
52 INSERT INTO num_exp_sub VALUES (0,8,'-74881');
53 INSERT INTO num_exp_mul VALUES (0,8,'0');
54 INSERT INTO num_exp_div VALUES (0,8,'0');
55 INSERT INTO num_exp_add VALUES (0,9,'-24926804.045047420');
56 INSERT INTO num_exp_sub VALUES (0,9,'24926804.045047420');
57 INSERT INTO num_exp_mul VALUES (0,9,'0');
58 INSERT INTO num_exp_div VALUES (0,9,'0');
59 INSERT INTO num_exp_add VALUES (1,0,'0');
60 INSERT INTO num_exp_sub VALUES (1,0,'0');
61 INSERT INTO num_exp_mul VALUES (1,0,'0');
62 INSERT INTO num_exp_div VALUES (1,0,'NaN');
63 INSERT INTO num_exp_add VALUES (1,1,'0');
64 INSERT INTO num_exp_sub VALUES (1,1,'0');
65 INSERT INTO num_exp_mul VALUES (1,1,'0');
66 INSERT INTO num_exp_div VALUES (1,1,'NaN');
67 INSERT INTO num_exp_add VALUES (1,2,'-34338492.215397047');
68 INSERT INTO num_exp_sub VALUES (1,2,'34338492.215397047');
69 INSERT INTO num_exp_mul VALUES (1,2,'0');
70 INSERT INTO num_exp_div VALUES (1,2,'0');
71 INSERT INTO num_exp_add VALUES (1,3,'4.31');
72 INSERT INTO num_exp_sub VALUES (1,3,'-4.31');
73 INSERT INTO num_exp_mul VALUES (1,3,'0');
74 INSERT INTO num_exp_div VALUES (1,3,'0');
75 INSERT INTO num_exp_add VALUES (1,4,'7799461.4119');
76 INSERT INTO num_exp_sub VALUES (1,4,'-7799461.4119');
77 INSERT INTO num_exp_mul VALUES (1,4,'0');
78 INSERT INTO num_exp_div VALUES (1,4,'0');
79 INSERT INTO num_exp_add VALUES (1,5,'16397.038491');
80 INSERT INTO num_exp_sub VALUES (1,5,'-16397.038491');
81 INSERT INTO num_exp_mul VALUES (1,5,'0');
82 INSERT INTO num_exp_div VALUES (1,5,'0');
83 INSERT INTO num_exp_add VALUES (1,6,'93901.57763026');
84 INSERT INTO num_exp_sub VALUES (1,6,'-93901.57763026');
85 INSERT INTO num_exp_mul VALUES (1,6,'0');
86 INSERT INTO num_exp_div VALUES (1,6,'0');
87 INSERT INTO num_exp_add VALUES (1,7,'-83028485');
88 INSERT INTO num_exp_sub VALUES (1,7,'83028485');
89 INSERT INTO num_exp_mul VALUES (1,7,'0');
90 INSERT INTO num_exp_div VALUES (1,7,'0');
91 INSERT INTO num_exp_add VALUES (1,8,'74881');
92 INSERT INTO num_exp_sub VALUES (1,8,'-74881');
93 INSERT INTO num_exp_mul VALUES (1,8,'0');
94 INSERT INTO num_exp_div VALUES (1,8,'0');
95 INSERT INTO num_exp_add VALUES (1,9,'-24926804.045047420');
96 INSERT INTO num_exp_sub VALUES (1,9,'24926804.045047420');
97 INSERT INTO num_exp_mul VALUES (1,9,'0');
98 INSERT INTO num_exp_div VALUES (1,9,'0');
99 INSERT INTO num_exp_add VALUES (2,0,'-34338492.215397047');
100 INSERT INTO num_exp_sub VALUES (2,0,'-34338492.215397047');
101 INSERT INTO num_exp_mul VALUES (2,0,'0');
102 INSERT INTO num_exp_div VALUES (2,0,'NaN');
103 INSERT INTO num_exp_add VALUES (2,1,'-34338492.215397047');
104 INSERT INTO num_exp_sub VALUES (2,1,'-34338492.215397047');
105 INSERT INTO num_exp_mul VALUES (2,1,'0');
106 INSERT INTO num_exp_div VALUES (2,1,'NaN');
107 INSERT INTO num_exp_add VALUES (2,2,'-68676984.430794094');
108 INSERT INTO num_exp_sub VALUES (2,2,'0');
109 INSERT INTO num_exp_mul VALUES (2,2,'1179132047626883.596862135856320209');
110 INSERT INTO num_exp_div VALUES (2,2,'1.00000000000000000000');
111 INSERT INTO num_exp_add VALUES (2,3,'-34338487.905397047');
112 INSERT INTO num_exp_sub VALUES (2,3,'-34338496.525397047');
113 INSERT INTO num_exp_mul VALUES (2,3,'-147998901.44836127257');
114 INSERT INTO num_exp_div VALUES (2,3,'-7967167.56737750510440835266');
115 INSERT INTO num_exp_add VALUES (2,4,'-26539030.803497047');
116 INSERT INTO num_exp_sub VALUES (2,4,'-42137953.627297047');
117 INSERT INTO num_exp_mul VALUES (2,4,'-267821744976817.8111137106593');
118 INSERT INTO num_exp_div VALUES (2,4,'-4.40267480046830116685');
119 INSERT INTO num_exp_add VALUES (2,5,'-34322095.176906047');
120 INSERT INTO num_exp_sub VALUES (2,5,'-34354889.253888047');
121 INSERT INTO num_exp_mul VALUES (2,5,'-563049578578.769242506736077');
122 INSERT INTO num_exp_div VALUES (2,5,'-2094.18866914563535496429');
123 INSERT INTO num_exp_add VALUES (2,6,'-34244590.637766787');
124 INSERT INTO num_exp_sub VALUES (2,6,'-34432393.793027307');
125 INSERT INTO num_exp_mul VALUES (2,6,'-3224438592470.18449811926184222');
126 INSERT INTO num_exp_div VALUES (2,6,'-365.68599891479766440940');
127 INSERT INTO num_exp_add VALUES (2,7,'-117366977.215397047');
128 INSERT INTO num_exp_sub VALUES (2,7,'48689992.784602953');
129 INSERT INTO num_exp_mul VALUES (2,7,'2851072985828710.485883795');
130 INSERT INTO num_exp_div VALUES (2,7,'.41357483778485235518');
131 INSERT INTO num_exp_add VALUES (2,8,'-34263611.215397047');
132 INSERT INTO num_exp_sub VALUES (2,8,'-34413373.215397047');
133 INSERT INTO num_exp_mul VALUES (2,8,'-2571300635581.146276407');
134 INSERT INTO num_exp_div VALUES (2,8,'-458.57416721727870888476');
135 INSERT INTO num_exp_add VALUES (2,9,'-59265296.260444467');
136 INSERT INTO num_exp_sub VALUES (2,9,'-9411688.170349627');
137 INSERT INTO num_exp_mul VALUES (2,9,'855948866655588.453741509242968740');
138 INSERT INTO num_exp_div VALUES (2,9,'1.37757299946438931811');
139 INSERT INTO num_exp_add VALUES (3,0,'4.31');
140 INSERT INTO num_exp_sub VALUES (3,0,'4.31');
141 INSERT INTO num_exp_mul VALUES (3,0,'0');
142 INSERT INTO num_exp_div VALUES (3,0,'NaN');
143 INSERT INTO num_exp_add VALUES (3,1,'4.31');
144 INSERT INTO num_exp_sub VALUES (3,1,'4.31');
145 INSERT INTO num_exp_mul VALUES (3,1,'0');
146 INSERT INTO num_exp_div VALUES (3,1,'NaN');
147 INSERT INTO num_exp_add VALUES (3,2,'-34338487.905397047');
148 INSERT INTO num_exp_sub VALUES (3,2,'34338496.525397047');
149 INSERT INTO num_exp_mul VALUES (3,2,'-147998901.44836127257');
150 INSERT INTO num_exp_div VALUES (3,2,'-.00000012551512084352');
151 INSERT INTO num_exp_add VALUES (3,3,'8.62');
152 INSERT INTO num_exp_sub VALUES (3,3,'0');
153 INSERT INTO num_exp_mul VALUES (3,3,'18.5761');
154 INSERT INTO num_exp_div VALUES (3,3,'1.00000000000000000000');
155 INSERT INTO num_exp_add VALUES (3,4,'7799465.7219');
156 INSERT INTO num_exp_sub VALUES (3,4,'-7799457.1019');
157 INSERT INTO num_exp_mul VALUES (3,4,'33615678.685289');
158 INSERT INTO num_exp_div VALUES (3,4,'.00000055260225961552');
159 INSERT INTO num_exp_add VALUES (3,5,'16401.348491');
160 INSERT INTO num_exp_sub VALUES (3,5,'-16392.728491');
161 INSERT INTO num_exp_mul VALUES (3,5,'70671.23589621');
162 INSERT INTO num_exp_div VALUES (3,5,'.00026285234387695504');
163 INSERT INTO num_exp_add VALUES (3,6,'93905.88763026');
164 INSERT INTO num_exp_sub VALUES (3,6,'-93897.26763026');
165 INSERT INTO num_exp_mul VALUES (3,6,'404715.7995864206');
166 INSERT INTO num_exp_div VALUES (3,6,'.00004589912234457595');
167 INSERT INTO num_exp_add VALUES (3,7,'-83028480.69');
168 INSERT INTO num_exp_sub VALUES (3,7,'83028489.31');
169 INSERT INTO num_exp_mul VALUES (3,7,'-357852770.35');
170 INSERT INTO num_exp_div VALUES (3,7,'-.00000005190989574240');
171 INSERT INTO num_exp_add VALUES (3,8,'74885.31');
172 INSERT INTO num_exp_sub VALUES (3,8,'-74876.69');
173 INSERT INTO num_exp_mul VALUES (3,8,'322737.11');
174 INSERT INTO num_exp_div VALUES (3,8,'.00005755799201399553');
175 INSERT INTO num_exp_add VALUES (3,9,'-24926799.735047420');
176 INSERT INTO num_exp_sub VALUES (3,9,'24926808.355047420');
177 INSERT INTO num_exp_mul VALUES (3,9,'-107434525.43415438020');
178 INSERT INTO num_exp_div VALUES (3,9,'-.00000017290624149854');
179 INSERT INTO num_exp_add VALUES (4,0,'7799461.4119');
180 INSERT INTO num_exp_sub VALUES (4,0,'7799461.4119');
181 INSERT INTO num_exp_mul VALUES (4,0,'0');
182 INSERT INTO num_exp_div VALUES (4,0,'NaN');
183 INSERT INTO num_exp_add VALUES (4,1,'7799461.4119');
184 INSERT INTO num_exp_sub VALUES (4,1,'7799461.4119');
185 INSERT INTO num_exp_mul VALUES (4,1,'0');
186 INSERT INTO num_exp_div VALUES (4,1,'NaN');
187 INSERT INTO num_exp_add VALUES (4,2,'-26539030.803497047');
188 INSERT INTO num_exp_sub VALUES (4,2,'42137953.627297047');
189 INSERT INTO num_exp_mul VALUES (4,2,'-267821744976817.8111137106593');
190 INSERT INTO num_exp_div VALUES (4,2,'-.22713465002993920385');
191 INSERT INTO num_exp_add VALUES (4,3,'7799465.7219');
192 INSERT INTO num_exp_sub VALUES (4,3,'7799457.1019');
193 INSERT INTO num_exp_mul VALUES (4,3,'33615678.685289');
194 INSERT INTO num_exp_div VALUES (4,3,'1809619.81714617169373549883');
195 INSERT INTO num_exp_add VALUES (4,4,'15598922.8238');
196 INSERT INTO num_exp_sub VALUES (4,4,'0');
197 INSERT INTO num_exp_mul VALUES (4,4,'60831598315717.14146161');
198 INSERT INTO num_exp_div VALUES (4,4,'1.00000000000000000000');
199 INSERT INTO num_exp_add VALUES (4,5,'7815858.450391');
200 INSERT INTO num_exp_sub VALUES (4,5,'7783064.373409');
201 INSERT INTO num_exp_mul VALUES (4,5,'127888068979.9935054429');
202 INSERT INTO num_exp_div VALUES (4,5,'475.66281046305802686061');
203 INSERT INTO num_exp_add VALUES (4,6,'7893362.98953026');
204 INSERT INTO num_exp_sub VALUES (4,6,'7705559.83426974');
205 INSERT INTO num_exp_mul VALUES (4,6,'732381731243.745115764094');
206 INSERT INTO num_exp_div VALUES (4,6,'83.05996138436129499606');
207 INSERT INTO num_exp_add VALUES (4,7,'-75229023.5881');
208 INSERT INTO num_exp_sub VALUES (4,7,'90827946.4119');
209 INSERT INTO num_exp_mul VALUES (4,7,'-647577464846017.9715');
210 INSERT INTO num_exp_div VALUES (4,7,'-.09393717604145131637');
211 INSERT INTO num_exp_add VALUES (4,8,'7874342.4119');
212 INSERT INTO num_exp_sub VALUES (4,8,'7724580.4119');
213 INSERT INTO num_exp_mul VALUES (4,8,'584031469984.4839');
214 INSERT INTO num_exp_div VALUES (4,8,'104.15808298366741897143');
215 INSERT INTO num_exp_add VALUES (4,9,'-17127342.633147420');
216 INSERT INTO num_exp_sub VALUES (4,9,'32726265.456947420');
217 INSERT INTO num_exp_mul VALUES (4,9,'-194415646271340.1815956522980');
218 INSERT INTO num_exp_div VALUES (4,9,'-.31289456112403769409');
219 INSERT INTO num_exp_add VALUES (5,0,'16397.038491');
220 INSERT INTO num_exp_sub VALUES (5,0,'16397.038491');
221 INSERT INTO num_exp_mul VALUES (5,0,'0');
222 INSERT INTO num_exp_div VALUES (5,0,'NaN');
223 INSERT INTO num_exp_add VALUES (5,1,'16397.038491');
224 INSERT INTO num_exp_sub VALUES (5,1,'16397.038491');
225 INSERT INTO num_exp_mul VALUES (5,1,'0');
226 INSERT INTO num_exp_div VALUES (5,1,'NaN');
227 INSERT INTO num_exp_add VALUES (5,2,'-34322095.176906047');
228 INSERT INTO num_exp_sub VALUES (5,2,'34354889.253888047');
229 INSERT INTO num_exp_mul VALUES (5,2,'-563049578578.769242506736077');
230 INSERT INTO num_exp_div VALUES (5,2,'-.00047751189505192446');
231 INSERT INTO num_exp_add VALUES (5,3,'16401.348491');
232 INSERT INTO num_exp_sub VALUES (5,3,'16392.728491');
233 INSERT INTO num_exp_mul VALUES (5,3,'70671.23589621');
234 INSERT INTO num_exp_div VALUES (5,3,'3804.41728329466357308584');
235 INSERT INTO num_exp_add VALUES (5,4,'7815858.450391');
236 INSERT INTO num_exp_sub VALUES (5,4,'-7783064.373409');
237 INSERT INTO num_exp_mul VALUES (5,4,'127888068979.9935054429');
238 INSERT INTO num_exp_div VALUES (5,4,'.00210232958726897192');
239 INSERT INTO num_exp_add VALUES (5,5,'32794.076982');
240 INSERT INTO num_exp_sub VALUES (5,5,'0');
241 INSERT INTO num_exp_mul VALUES (5,5,'268862871.275335557081');
242 INSERT INTO num_exp_div VALUES (5,5,'1.00000000000000000000');
243 INSERT INTO num_exp_add VALUES (5,6,'110298.61612126');
244 INSERT INTO num_exp_sub VALUES (5,6,'-77504.53913926');
245 INSERT INTO num_exp_mul VALUES (5,6,'1539707782.76899778633766');
246 INSERT INTO num_exp_div VALUES (5,6,'.17461941433576102689');
247 INSERT INTO num_exp_add VALUES (5,7,'-83012087.961509');
248 INSERT INTO num_exp_sub VALUES (5,7,'83044882.038491');
249 INSERT INTO num_exp_mul VALUES (5,7,'-1361421264394.416135');
250 INSERT INTO num_exp_div VALUES (5,7,'-.00019748690453643710');
251 INSERT INTO num_exp_add VALUES (5,8,'91278.038491');
252 INSERT INTO num_exp_sub VALUES (5,8,'-58483.961509');
253 INSERT INTO num_exp_mul VALUES (5,8,'1227826639.244571');
254 INSERT INTO num_exp_div VALUES (5,8,'.21897461960978085228');
255 INSERT INTO num_exp_add VALUES (5,9,'-24910407.006556420');
256 INSERT INTO num_exp_sub VALUES (5,9,'24943201.083538420');
257 INSERT INTO num_exp_mul VALUES (5,9,'-408725765384.257043660243220');
258 INSERT INTO num_exp_div VALUES (5,9,'-.00065780749354660427');
259 INSERT INTO num_exp_add VALUES (6,0,'93901.57763026');
260 INSERT INTO num_exp_sub VALUES (6,0,'93901.57763026');
261 INSERT INTO num_exp_mul VALUES (6,0,'0');
262 INSERT INTO num_exp_div VALUES (6,0,'NaN');
263 INSERT INTO num_exp_add VALUES (6,1,'93901.57763026');
264 INSERT INTO num_exp_sub VALUES (6,1,'93901.57763026');
265 INSERT INTO num_exp_mul VALUES (6,1,'0');
266 INSERT INTO num_exp_div VALUES (6,1,'NaN');
267 INSERT INTO num_exp_add VALUES (6,2,'-34244590.637766787');
268 INSERT INTO num_exp_sub VALUES (6,2,'34432393.793027307');
269 INSERT INTO num_exp_mul VALUES (6,2,'-3224438592470.18449811926184222');
270 INSERT INTO num_exp_div VALUES (6,2,'-.00273458651128995823');
271 INSERT INTO num_exp_add VALUES (6,3,'93905.88763026');
272 INSERT INTO num_exp_sub VALUES (6,3,'93897.26763026');
273 INSERT INTO num_exp_mul VALUES (6,3,'404715.7995864206');
274 INSERT INTO num_exp_div VALUES (6,3,'21786.90896293735498839907');
275 INSERT INTO num_exp_add VALUES (6,4,'7893362.98953026');
276 INSERT INTO num_exp_sub VALUES (6,4,'-7705559.83426974');
277 INSERT INTO num_exp_mul VALUES (6,4,'732381731243.745115764094');
278 INSERT INTO num_exp_div VALUES (6,4,'.01203949512295682469');
279 INSERT INTO num_exp_add VALUES (6,5,'110298.61612126');
280 INSERT INTO num_exp_sub VALUES (6,5,'77504.53913926');
281 INSERT INTO num_exp_mul VALUES (6,5,'1539707782.76899778633766');
282 INSERT INTO num_exp_div VALUES (6,5,'5.72674008674192359679');
283 INSERT INTO num_exp_add VALUES (6,6,'187803.15526052');
284 INSERT INTO num_exp_sub VALUES (6,6,'0');
285 INSERT INTO num_exp_mul VALUES (6,6,'8817506281.4517452372676676');
286 INSERT INTO num_exp_div VALUES (6,6,'1.00000000000000000000');
287 INSERT INTO num_exp_add VALUES (6,7,'-82934583.42236974');
288 INSERT INTO num_exp_sub VALUES (6,7,'83122386.57763026');
289 INSERT INTO num_exp_mul VALUES (6,7,'-7796505729750.37795610');
290 INSERT INTO num_exp_div VALUES (6,7,'-.00113095617281538980');
291 INSERT INTO num_exp_add VALUES (6,8,'168782.57763026');
292 INSERT INTO num_exp_sub VALUES (6,8,'19020.57763026');
293 INSERT INTO num_exp_mul VALUES (6,8,'7031444034.53149906');
294 INSERT INTO num_exp_div VALUES (6,8,'1.25401073209839612184');
295 INSERT INTO num_exp_add VALUES (6,9,'-24832902.467417160');
296 INSERT INTO num_exp_sub VALUES (6,9,'25020705.622677680');
297 INSERT INTO num_exp_mul VALUES (6,9,'-2340666225110.29929521292692920');
298 INSERT INTO num_exp_div VALUES (6,9,'-.00376709254265256789');
299 INSERT INTO num_exp_add VALUES (7,0,'-83028485');
300 INSERT INTO num_exp_sub VALUES (7,0,'-83028485');
301 INSERT INTO num_exp_mul VALUES (7,0,'0');
302 INSERT INTO num_exp_div VALUES (7,0,'NaN');
303 INSERT INTO num_exp_add VALUES (7,1,'-83028485');
304 INSERT INTO num_exp_sub VALUES (7,1,'-83028485');
305 INSERT INTO num_exp_mul VALUES (7,1,'0');
306 INSERT INTO num_exp_div VALUES (7,1,'NaN');
307 INSERT INTO num_exp_add VALUES (7,2,'-117366977.215397047');
308 INSERT INTO num_exp_sub VALUES (7,2,'-48689992.784602953');
309 INSERT INTO num_exp_mul VALUES (7,2,'2851072985828710.485883795');
310 INSERT INTO num_exp_div VALUES (7,2,'2.41794207151503385700');
311 INSERT INTO num_exp_add VALUES (7,3,'-83028480.69');
312 INSERT INTO num_exp_sub VALUES (7,3,'-83028489.31');
313 INSERT INTO num_exp_mul VALUES (7,3,'-357852770.35');
314 INSERT INTO num_exp_div VALUES (7,3,'-19264149.65197215777262180974');
315 INSERT INTO num_exp_add VALUES (7,4,'-75229023.5881');
316 INSERT INTO num_exp_sub VALUES (7,4,'-90827946.4119');
317 INSERT INTO num_exp_mul VALUES (7,4,'-647577464846017.9715');
318 INSERT INTO num_exp_div VALUES (7,4,'-10.64541262725136247686');
319 INSERT INTO num_exp_add VALUES (7,5,'-83012087.961509');
320 INSERT INTO num_exp_sub VALUES (7,5,'-83044882.038491');
321 INSERT INTO num_exp_mul VALUES (7,5,'-1361421264394.416135');
322 INSERT INTO num_exp_div VALUES (7,5,'-5063.62688881730941836574');
323 INSERT INTO num_exp_add VALUES (7,6,'-82934583.42236974');
324 INSERT INTO num_exp_sub VALUES (7,6,'-83122386.57763026');
325 INSERT INTO num_exp_mul VALUES (7,6,'-7796505729750.37795610');
326 INSERT INTO num_exp_div VALUES (7,6,'-884.20756174009028770294');
327 INSERT INTO num_exp_add VALUES (7,7,'-166056970');
328 INSERT INTO num_exp_sub VALUES (7,7,'0');
329 INSERT INTO num_exp_mul VALUES (7,7,'6893729321395225');
330 INSERT INTO num_exp_div VALUES (7,7,'1.00000000000000000000');
331 INSERT INTO num_exp_add VALUES (7,8,'-82953604');
332 INSERT INTO num_exp_sub VALUES (7,8,'-83103366');
333 INSERT INTO num_exp_mul VALUES (7,8,'-6217255985285');
334 INSERT INTO num_exp_div VALUES (7,8,'-1108.80577182462841041118');
335 INSERT INTO num_exp_add VALUES (7,9,'-107955289.045047420');
336 INSERT INTO num_exp_sub VALUES (7,9,'-58101680.954952580');
337 INSERT INTO num_exp_mul VALUES (7,9,'2069634775752159.035758700');
338 INSERT INTO num_exp_div VALUES (7,9,'3.33089171198810413382');
339 INSERT INTO num_exp_add VALUES (8,0,'74881');
340 INSERT INTO num_exp_sub VALUES (8,0,'74881');
341 INSERT INTO num_exp_mul VALUES (8,0,'0');
342 INSERT INTO num_exp_div VALUES (8,0,'NaN');
343 INSERT INTO num_exp_add VALUES (8,1,'74881');
344 INSERT INTO num_exp_sub VALUES (8,1,'74881');
345 INSERT INTO num_exp_mul VALUES (8,1,'0');
346 INSERT INTO num_exp_div VALUES (8,1,'NaN');
347 INSERT INTO num_exp_add VALUES (8,2,'-34263611.215397047');
348 INSERT INTO num_exp_sub VALUES (8,2,'34413373.215397047');
349 INSERT INTO num_exp_mul VALUES (8,2,'-2571300635581.146276407');
350 INSERT INTO num_exp_div VALUES (8,2,'-.00218067233500788615');
351 INSERT INTO num_exp_add VALUES (8,3,'74885.31');
352 INSERT INTO num_exp_sub VALUES (8,3,'74876.69');
353 INSERT INTO num_exp_mul VALUES (8,3,'322737.11');
354 INSERT INTO num_exp_div VALUES (8,3,'17373.78190255220417633410');
355 INSERT INTO num_exp_add VALUES (8,4,'7874342.4119');
356 INSERT INTO num_exp_sub VALUES (8,4,'-7724580.4119');
357 INSERT INTO num_exp_mul VALUES (8,4,'584031469984.4839');
358 INSERT INTO num_exp_div VALUES (8,4,'.00960079113741758956');
359 INSERT INTO num_exp_add VALUES (8,5,'91278.038491');
360 INSERT INTO num_exp_sub VALUES (8,5,'58483.961509');
361 INSERT INTO num_exp_mul VALUES (8,5,'1227826639.244571');
362 INSERT INTO num_exp_div VALUES (8,5,'4.56673929509287019456');
363 INSERT INTO num_exp_add VALUES (8,6,'168782.57763026');
364 INSERT INTO num_exp_sub VALUES (8,6,'-19020.57763026');
365 INSERT INTO num_exp_mul VALUES (8,6,'7031444034.53149906');
366 INSERT INTO num_exp_div VALUES (8,6,'.79744134113322314424');
367 INSERT INTO num_exp_add VALUES (8,7,'-82953604');
368 INSERT INTO num_exp_sub VALUES (8,7,'83103366');
369 INSERT INTO num_exp_mul VALUES (8,7,'-6217255985285');
370 INSERT INTO num_exp_div VALUES (8,7,'-.00090187120721280172');
371 INSERT INTO num_exp_add VALUES (8,8,'149762');
372 INSERT INTO num_exp_sub VALUES (8,8,'0');
373 INSERT INTO num_exp_mul VALUES (8,8,'5607164161');
374 INSERT INTO num_exp_div VALUES (8,8,'1.00000000000000000000');
375 INSERT INTO num_exp_add VALUES (8,9,'-24851923.045047420');
376 INSERT INTO num_exp_sub VALUES (8,9,'25001685.045047420');
377 INSERT INTO num_exp_mul VALUES (8,9,'-1866544013697.195857020');
378 INSERT INTO num_exp_div VALUES (8,9,'-.00300403532938582735');
379 INSERT INTO num_exp_add VALUES (9,0,'-24926804.045047420');
380 INSERT INTO num_exp_sub VALUES (9,0,'-24926804.045047420');
381 INSERT INTO num_exp_mul VALUES (9,0,'0');
382 INSERT INTO num_exp_div VALUES (9,0,'NaN');
383 INSERT INTO num_exp_add VALUES (9,1,'-24926804.045047420');
384 INSERT INTO num_exp_sub VALUES (9,1,'-24926804.045047420');
385 INSERT INTO num_exp_mul VALUES (9,1,'0');
386 INSERT INTO num_exp_div VALUES (9,1,'NaN');
387 INSERT INTO num_exp_add VALUES (9,2,'-59265296.260444467');
388 INSERT INTO num_exp_sub VALUES (9,2,'9411688.170349627');
389 INSERT INTO num_exp_mul VALUES (9,2,'855948866655588.453741509242968740');
390 INSERT INTO num_exp_div VALUES (9,2,'.72591434384152961526');
391 INSERT INTO num_exp_add VALUES (9,3,'-24926799.735047420');
392 INSERT INTO num_exp_sub VALUES (9,3,'-24926808.355047420');
393 INSERT INTO num_exp_mul VALUES (9,3,'-107434525.43415438020');
394 INSERT INTO num_exp_div VALUES (9,3,'-5783481.21694835730858468677');
395 INSERT INTO num_exp_add VALUES (9,4,'-17127342.633147420');
396 INSERT INTO num_exp_sub VALUES (9,4,'-32726265.456947420');
397 INSERT INTO num_exp_mul VALUES (9,4,'-194415646271340.1815956522980');
398 INSERT INTO num_exp_div VALUES (9,4,'-3.19596478892958416484');
399 INSERT INTO num_exp_add VALUES (9,5,'-24910407.006556420');
400 INSERT INTO num_exp_sub VALUES (9,5,'-24943201.083538420');
401 INSERT INTO num_exp_mul VALUES (9,5,'-408725765384.257043660243220');
402 INSERT INTO num_exp_div VALUES (9,5,'-1520.20159364322004505807');
403 INSERT INTO num_exp_add VALUES (9,6,'-24832902.467417160');
404 INSERT INTO num_exp_sub VALUES (9,6,'-25020705.622677680');
405 INSERT INTO num_exp_mul VALUES (9,6,'-2340666225110.29929521292692920');
406 INSERT INTO num_exp_div VALUES (9,6,'-265.45671195426965751280');
407 INSERT INTO num_exp_add VALUES (9,7,'-107955289.045047420');
408 INSERT INTO num_exp_sub VALUES (9,7,'58101680.954952580');
409 INSERT INTO num_exp_mul VALUES (9,7,'2069634775752159.035758700');
410 INSERT INTO num_exp_div VALUES (9,7,'.30021990699995814689');
411 INSERT INTO num_exp_add VALUES (9,8,'-24851923.045047420');
412 INSERT INTO num_exp_sub VALUES (9,8,'-25001685.045047420');
413 INSERT INTO num_exp_mul VALUES (9,8,'-1866544013697.195857020');
414 INSERT INTO num_exp_div VALUES (9,8,'-332.88556569820675471748');
415 INSERT INTO num_exp_add VALUES (9,9,'-49853608.090094840');
416 INSERT INTO num_exp_sub VALUES (9,9,'0');
417 INSERT INTO num_exp_mul VALUES (9,9,'621345559900192.420120630048656400');
418 INSERT INTO num_exp_div VALUES (9,9,'1.00000000000000000000');
419 COMMIT TRANSACTION;
420 BEGIN TRANSACTION;
421 INSERT INTO num_exp_sqrt VALUES (0,'0');
422 INSERT INTO num_exp_sqrt VALUES (1,'0');
423 INSERT INTO num_exp_sqrt VALUES (2,'5859.90547836712524903505');
424 INSERT INTO num_exp_sqrt VALUES (3,'2.07605394920266944396');
425 INSERT INTO num_exp_sqrt VALUES (4,'2792.75158435189147418923');
426 INSERT INTO num_exp_sqrt VALUES (5,'128.05092147657509145473');
427 INSERT INTO num_exp_sqrt VALUES (6,'306.43364311096782703406');
428 INSERT INTO num_exp_sqrt VALUES (7,'9111.99676251039939975230');
429 INSERT INTO num_exp_sqrt VALUES (8,'273.64392922189960397542');
430 INSERT INTO num_exp_sqrt VALUES (9,'4992.67503899937593364766');
431 COMMIT TRANSACTION;
432 BEGIN TRANSACTION;
433 INSERT INTO num_exp_ln VALUES (0,'NaN');
434 INSERT INTO num_exp_ln VALUES (1,'NaN');
435 INSERT INTO num_exp_ln VALUES (2,'17.35177750493897715514');
436 INSERT INTO num_exp_ln VALUES (3,'1.46093790411565641971');
437 INSERT INTO num_exp_ln VALUES (4,'15.86956523951936572464');
438 INSERT INTO num_exp_ln VALUES (5,'9.70485601768871834038');
439 INSERT INTO num_exp_ln VALUES (6,'11.45000246622944403127');
440 INSERT INTO num_exp_ln VALUES (7,'18.23469429965478772991');
441 INSERT INTO num_exp_ln VALUES (8,'11.22365546576315513668');
442 INSERT INTO num_exp_ln VALUES (9,'17.03145425013166006962');
443 COMMIT TRANSACTION;
444 BEGIN TRANSACTION;
445 INSERT INTO num_exp_log10 VALUES (0,'NaN');
446 INSERT INTO num_exp_log10 VALUES (1,'NaN');
447 INSERT INTO num_exp_log10 VALUES (2,'7.53578122160797276459');
448 INSERT INTO num_exp_log10 VALUES (3,'.63447727016073160075');
449 INSERT INTO num_exp_log10 VALUES (4,'6.89206461372691743345');
450 INSERT INTO num_exp_log10 VALUES (5,'4.21476541614777768626');
451 INSERT INTO num_exp_log10 VALUES (6,'4.97267288886207207671');
452 INSERT INTO num_exp_log10 VALUES (7,'7.91922711353275546914');
453 INSERT INTO num_exp_log10 VALUES (8,'4.87437163556421004138');
454 INSERT INTO num_exp_log10 VALUES (9,'7.39666659961986567059');
455 COMMIT TRANSACTION;
456 BEGIN TRANSACTION;
457 INSERT INTO num_exp_power_10_ln VALUES (0,'NaN');
458 INSERT INTO num_exp_power_10_ln VALUES (1,'NaN');
459 INSERT INTO num_exp_power_10_ln VALUES (2,'224790267919917955.13261618583642653184');
460 INSERT INTO num_exp_power_10_ln VALUES (3,'28.90266599445155957393');
461 INSERT INTO num_exp_power_10_ln VALUES (4,'7405685069594999.07733999469386277636');
462 INSERT INTO num_exp_power_10_ln VALUES (5,'5068226527.32127265408584640098');
463 INSERT INTO num_exp_power_10_ln VALUES (6,'281839893606.99372343357047819067');
464 INSERT INTO num_exp_power_10_ln VALUES (7,'1716699575118597095.42330819910640247627');
465 INSERT INTO num_exp_power_10_ln VALUES (8,'167361463828.07491320069016125952');
466 INSERT INTO num_exp_power_10_ln VALUES (9,'107511333880052007.04141124673540337457');
467 COMMIT TRANSACTION;
468 BEGIN TRANSACTION;
469 INSERT INTO num_data VALUES (0, '0');
470 INSERT INTO num_data VALUES (1, '0');
471 INSERT INTO num_data VALUES (2, '-34338492.215397047');
472 INSERT INTO num_data VALUES (3, '4.31');
473 INSERT INTO num_data VALUES (4, '7799461.4119');
474 INSERT INTO num_data VALUES (5, '16397.038491');
475 INSERT INTO num_data VALUES (6, '93901.57763026');
476 INSERT INTO num_data VALUES (7, '-83028485');
477 INSERT INTO num_data VALUES (8, '74881');
478 INSERT INTO num_data VALUES (9, '-24926804.045047420');
479 COMMIT TRANSACTION;
480 -- ******************************
481 -- * Create indices for faster checks
482 -- ******************************
483 CREATE UNIQUE INDEX num_exp_add_idx ON num_exp_add (id1, id2);
484 CREATE UNIQUE INDEX num_exp_sub_idx ON num_exp_sub (id1, id2);
485 CREATE UNIQUE INDEX num_exp_div_idx ON num_exp_div (id1, id2);
486 CREATE UNIQUE INDEX num_exp_mul_idx ON num_exp_mul (id1, id2);
487 CREATE UNIQUE INDEX num_exp_sqrt_idx ON num_exp_sqrt (id);
488 CREATE UNIQUE INDEX num_exp_ln_idx ON num_exp_ln (id);
489 CREATE UNIQUE INDEX num_exp_log10_idx ON num_exp_log10 (id);
490 CREATE UNIQUE INDEX num_exp_power_10_ln_idx ON num_exp_power_10_ln (id);
491 VACUUM ANALYZE num_exp_add;
492 VACUUM ANALYZE num_exp_sub;
493 VACUUM ANALYZE num_exp_div;
494 VACUUM ANALYZE num_exp_mul;
495 VACUUM ANALYZE num_exp_sqrt;
496 VACUUM ANALYZE num_exp_ln;
497 VACUUM ANALYZE num_exp_log10;
498 VACUUM ANALYZE num_exp_power_10_ln;
499 -- ******************************
500 -- * Now check the behaviour of the NUMERIC type
501 -- ******************************
502 -- ******************************
503 -- * Addition check
504 -- ******************************
505 DELETE FROM num_result;
506 INSERT INTO num_result SELECT t1.id, t2.id, t1.val + t2.val
507 FROM num_data t1, num_data t2;
508 SELECT t1.id1, t1.id2, t1.result, t2.expected
509 FROM num_result t1, num_exp_add t2
510 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
511 AND t1.result != t2.expected;
512 id1 | id2 | result | expected
513 -----+-----+--------+----------
514 (0 rows)
516 DELETE FROM num_result;
517 INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val + t2.val, 10)
518 FROM num_data t1, num_data t2;
519 SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 10) as expected
520 FROM num_result t1, num_exp_add t2
521 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
522 AND t1.result != round(t2.expected, 10);
523 id1 | id2 | result | expected
524 -----+-----+--------+----------
525 (0 rows)
527 -- ******************************
528 -- * Subtraction check
529 -- ******************************
530 DELETE FROM num_result;
531 INSERT INTO num_result SELECT t1.id, t2.id, t1.val - t2.val
532 FROM num_data t1, num_data t2;
533 SELECT t1.id1, t1.id2, t1.result, t2.expected
534 FROM num_result t1, num_exp_sub t2
535 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
536 AND t1.result != t2.expected;
537 id1 | id2 | result | expected
538 -----+-----+--------+----------
539 (0 rows)
541 DELETE FROM num_result;
542 INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val - t2.val, 40)
543 FROM num_data t1, num_data t2;
544 SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 40)
545 FROM num_result t1, num_exp_sub t2
546 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
547 AND t1.result != round(t2.expected, 40);
548 id1 | id2 | result | round
549 -----+-----+--------+-------
550 (0 rows)
552 -- ******************************
553 -- * Multiply check
554 -- ******************************
555 DELETE FROM num_result;
556 INSERT INTO num_result SELECT t1.id, t2.id, t1.val * t2.val
557 FROM num_data t1, num_data t2;
558 SELECT t1.id1, t1.id2, t1.result, t2.expected
559 FROM num_result t1, num_exp_mul t2
560 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
561 AND t1.result != t2.expected;
562 id1 | id2 | result | expected
563 -----+-----+--------+----------
564 (0 rows)
566 DELETE FROM num_result;
567 INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val * t2.val, 30)
568 FROM num_data t1, num_data t2;
569 SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 30) as expected
570 FROM num_result t1, num_exp_mul t2
571 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
572 AND t1.result != round(t2.expected, 30);
573 id1 | id2 | result | expected
574 -----+-----+--------+----------
575 (0 rows)
577 -- ******************************
578 -- * Division check
579 -- ******************************
580 DELETE FROM num_result;
581 INSERT INTO num_result SELECT t1.id, t2.id, t1.val / t2.val
582 FROM num_data t1, num_data t2
583 WHERE t2.val != '0.0';
584 SELECT t1.id1, t1.id2, t1.result, t2.expected
585 FROM num_result t1, num_exp_div t2
586 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
587 AND t1.result != t2.expected;
588 id1 | id2 | result | expected
589 -----+-----+--------+----------
590 (0 rows)
592 DELETE FROM num_result;
593 INSERT INTO num_result SELECT t1.id, t2.id, round(t1.val / t2.val, 80)
594 FROM num_data t1, num_data t2
595 WHERE t2.val != '0.0';
596 SELECT t1.id1, t1.id2, t1.result, round(t2.expected, 80) as expected
597 FROM num_result t1, num_exp_div t2
598 WHERE t1.id1 = t2.id1 AND t1.id2 = t2.id2
599 AND t1.result != round(t2.expected, 80);
600 id1 | id2 | result | expected
601 -----+-----+--------+----------
602 (0 rows)
604 -- ******************************
605 -- * Square root check
606 -- ******************************
607 DELETE FROM num_result;
608 INSERT INTO num_result SELECT id, 0, SQRT(ABS(val))
609 FROM num_data;
610 SELECT t1.id1, t1.result, t2.expected
611 FROM num_result t1, num_exp_sqrt t2
612 WHERE t1.id1 = t2.id
613 AND t1.result != t2.expected;
614 id1 | result | expected
615 -----+--------+----------
616 (0 rows)
618 -- ******************************
619 -- * Natural logarithm check
620 -- ******************************
621 DELETE FROM num_result;
622 INSERT INTO num_result SELECT id, 0, LN(ABS(val))
623 FROM num_data
624 WHERE val != '0.0';
625 SELECT t1.id1, t1.result, t2.expected
626 FROM num_result t1, num_exp_ln t2
627 WHERE t1.id1 = t2.id
628 AND t1.result != t2.expected;
629 id1 | result | expected
630 -----+--------+----------
631 (0 rows)
633 -- ******************************
634 -- * Logarithm base 10 check
635 -- ******************************
636 DELETE FROM num_result;
637 INSERT INTO num_result SELECT id, 0, LOG(numeric '10', ABS(val))
638 FROM num_data
639 WHERE val != '0.0';
640 SELECT t1.id1, t1.result, t2.expected
641 FROM num_result t1, num_exp_log10 t2
642 WHERE t1.id1 = t2.id
643 AND t1.result != t2.expected;
644 id1 | result | expected
645 -----+--------+----------
646 (0 rows)
648 -- ******************************
649 -- * POWER(10, LN(value)) check
650 -- ******************************
651 DELETE FROM num_result;
652 INSERT INTO num_result SELECT id, 0, POWER(numeric '10', LN(ABS(round(val,200))))
653 FROM num_data
654 WHERE val != '0.0';
655 SELECT t1.id1, t1.result, t2.expected
656 FROM num_result t1, num_exp_power_10_ln t2
657 WHERE t1.id1 = t2.id
658 AND t1.result != t2.expected;
659 id1 | result | expected
660 -----+--------+----------
661 (0 rows)
663 -- ******************************
664 -- * Check behavior with Inf and NaN inputs. It's easiest to handle these
665 -- * separately from the num_data framework used above, because some input
666 -- * combinations will throw errors.
667 -- ******************************
668 WITH v(x) AS
669 (VALUES('0'::numeric),('1'),('-1'),('4.2'),('inf'),('-inf'),('nan'))
670 SELECT x1, x2,
671 x1 + x2 AS sum,
672 x1 - x2 AS diff,
673 x1 * x2 AS prod
674 FROM v AS v1(x1), v AS v2(x2);
675 x1 | x2 | sum | diff | prod
676 -----------+-----------+-----------+-----------+-----------
677 0 | 0 | 0 | 0 | 0
678 0 | 1 | 1 | -1 | 0
679 0 | -1 | -1 | 1 | 0
680 0 | 4.2 | 4.2 | -4.2 | 0.0
681 0 | Infinity | Infinity | -Infinity | NaN
682 0 | -Infinity | -Infinity | Infinity | NaN
683 0 | NaN | NaN | NaN | NaN
684 1 | 0 | 1 | 1 | 0
685 1 | 1 | 2 | 0 | 1
686 1 | -1 | 0 | 2 | -1
687 1 | 4.2 | 5.2 | -3.2 | 4.2
688 1 | Infinity | Infinity | -Infinity | Infinity
689 1 | -Infinity | -Infinity | Infinity | -Infinity
690 1 | NaN | NaN | NaN | NaN
691 -1 | 0 | -1 | -1 | 0
692 -1 | 1 | 0 | -2 | -1
693 -1 | -1 | -2 | 0 | 1
694 -1 | 4.2 | 3.2 | -5.2 | -4.2
695 -1 | Infinity | Infinity | -Infinity | -Infinity
696 -1 | -Infinity | -Infinity | Infinity | Infinity
697 -1 | NaN | NaN | NaN | NaN
698 4.2 | 0 | 4.2 | 4.2 | 0.0
699 4.2 | 1 | 5.2 | 3.2 | 4.2
700 4.2 | -1 | 3.2 | 5.2 | -4.2
701 4.2 | 4.2 | 8.4 | 0.0 | 17.64
702 4.2 | Infinity | Infinity | -Infinity | Infinity
703 4.2 | -Infinity | -Infinity | Infinity | -Infinity
704 4.2 | NaN | NaN | NaN | NaN
705 Infinity | 0 | Infinity | Infinity | NaN
706 Infinity | 1 | Infinity | Infinity | Infinity
707 Infinity | -1 | Infinity | Infinity | -Infinity
708 Infinity | 4.2 | Infinity | Infinity | Infinity
709 Infinity | Infinity | Infinity | NaN | Infinity
710 Infinity | -Infinity | NaN | Infinity | -Infinity
711 Infinity | NaN | NaN | NaN | NaN
712 -Infinity | 0 | -Infinity | -Infinity | NaN
713 -Infinity | 1 | -Infinity | -Infinity | -Infinity
714 -Infinity | -1 | -Infinity | -Infinity | Infinity
715 -Infinity | 4.2 | -Infinity | -Infinity | -Infinity
716 -Infinity | Infinity | NaN | -Infinity | -Infinity
717 -Infinity | -Infinity | -Infinity | NaN | Infinity
718 -Infinity | NaN | NaN | NaN | NaN
719 NaN | 0 | NaN | NaN | NaN
720 NaN | 1 | NaN | NaN | NaN
721 NaN | -1 | NaN | NaN | NaN
722 NaN | 4.2 | NaN | NaN | NaN
723 NaN | Infinity | NaN | NaN | NaN
724 NaN | -Infinity | NaN | NaN | NaN
725 NaN | NaN | NaN | NaN | NaN
726 (49 rows)
728 WITH v(x) AS
729 (VALUES('0'::numeric),('1'),('-1'),('4.2'),('inf'),('-inf'),('nan'))
730 SELECT x1, x2,
731 x1 / x2 AS quot,
732 x1 % x2 AS mod,
733 div(x1, x2) AS div
734 FROM v AS v1(x1), v AS v2(x2) WHERE x2 != 0;
735 x1 | x2 | quot | mod | div
736 -----------+-----------+-------------------------+------+-----------
737 0 | 1 | 0.00000000000000000000 | 0 | 0
738 1 | 1 | 1.00000000000000000000 | 0 | 1
739 -1 | 1 | -1.00000000000000000000 | 0 | -1
740 4.2 | 1 | 4.2000000000000000 | 0.2 | 4
741 Infinity | 1 | Infinity | NaN | Infinity
742 -Infinity | 1 | -Infinity | NaN | -Infinity
743 NaN | 1 | NaN | NaN | NaN
744 0 | -1 | 0.00000000000000000000 | 0 | 0
745 1 | -1 | -1.00000000000000000000 | 0 | -1
746 -1 | -1 | 1.00000000000000000000 | 0 | 1
747 4.2 | -1 | -4.2000000000000000 | 0.2 | -4
748 Infinity | -1 | -Infinity | NaN | -Infinity
749 -Infinity | -1 | Infinity | NaN | Infinity
750 NaN | -1 | NaN | NaN | NaN
751 0 | 4.2 | 0.00000000000000000000 | 0.0 | 0
752 1 | 4.2 | 0.23809523809523809524 | 1.0 | 0
753 -1 | 4.2 | -0.23809523809523809524 | -1.0 | 0
754 4.2 | 4.2 | 1.00000000000000000000 | 0.0 | 1
755 Infinity | 4.2 | Infinity | NaN | Infinity
756 -Infinity | 4.2 | -Infinity | NaN | -Infinity
757 NaN | 4.2 | NaN | NaN | NaN
758 0 | Infinity | 0 | 0 | 0
759 1 | Infinity | 0 | 1 | 0
760 -1 | Infinity | 0 | -1 | 0
761 4.2 | Infinity | 0 | 4.2 | 0
762 Infinity | Infinity | NaN | NaN | NaN
763 -Infinity | Infinity | NaN | NaN | NaN
764 NaN | Infinity | NaN | NaN | NaN
765 0 | -Infinity | 0 | 0 | 0
766 1 | -Infinity | 0 | 1 | 0
767 -1 | -Infinity | 0 | -1 | 0
768 4.2 | -Infinity | 0 | 4.2 | 0
769 Infinity | -Infinity | NaN | NaN | NaN
770 -Infinity | -Infinity | NaN | NaN | NaN
771 NaN | -Infinity | NaN | NaN | NaN
772 0 | NaN | NaN | NaN | NaN
773 1 | NaN | NaN | NaN | NaN
774 -1 | NaN | NaN | NaN | NaN
775 4.2 | NaN | NaN | NaN | NaN
776 Infinity | NaN | NaN | NaN | NaN
777 -Infinity | NaN | NaN | NaN | NaN
778 NaN | NaN | NaN | NaN | NaN
779 (42 rows)
781 SELECT 'inf'::numeric / '0';
782 ERROR: division by zero
783 SELECT '-inf'::numeric / '0';
784 ERROR: division by zero
785 SELECT 'nan'::numeric / '0';
786 ?column?
787 ----------
788 NaN
789 (1 row)
791 SELECT '0'::numeric / '0';
792 ERROR: division by zero
793 SELECT 'inf'::numeric % '0';
794 ERROR: division by zero
795 SELECT '-inf'::numeric % '0';
796 ERROR: division by zero
797 SELECT 'nan'::numeric % '0';
798 ?column?
799 ----------
800 NaN
801 (1 row)
803 SELECT '0'::numeric % '0';
804 ERROR: division by zero
805 SELECT div('inf'::numeric, '0');
806 ERROR: division by zero
807 SELECT div('-inf'::numeric, '0');
808 ERROR: division by zero
809 SELECT div('nan'::numeric, '0');
810 div
811 -----
812 NaN
813 (1 row)
815 SELECT div('0'::numeric, '0');
816 ERROR: division by zero
817 WITH v(x) AS
818 (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('inf'),('-inf'),('nan'))
819 SELECT x, -x as minusx, abs(x), floor(x), ceil(x), sign(x), numeric_inc(x) as inc
820 FROM v;
821 x | minusx | abs | floor | ceil | sign | inc
822 -----------+-----------+----------+-----------+-----------+------+-----------
823 0 | 0 | 0 | 0 | 0 | 0 | 1
824 1 | -1 | 1 | 1 | 1 | 1 | 2
825 -1 | 1 | 1 | -1 | -1 | -1 | 0
826 4.2 | -4.2 | 4.2 | 4 | 5 | 1 | 5.2
827 -7.777 | 7.777 | 7.777 | -8 | -7 | -1 | -6.777
828 Infinity | -Infinity | Infinity | Infinity | Infinity | 1 | Infinity
829 -Infinity | Infinity | Infinity | -Infinity | -Infinity | -1 | -Infinity
830 NaN | NaN | NaN | NaN | NaN | NaN | NaN
831 (8 rows)
833 WITH v(x) AS
834 (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('inf'),('-inf'),('nan'))
835 SELECT x, round(x), round(x,1) as round1, trunc(x), trunc(x,1) as trunc1
836 FROM v;
837 x | round | round1 | trunc | trunc1
838 -----------+-----------+-----------+-----------+-----------
839 0 | 0 | 0.0 | 0 | 0.0
840 1 | 1 | 1.0 | 1 | 1.0
841 -1 | -1 | -1.0 | -1 | -1.0
842 4.2 | 4 | 4.2 | 4 | 4.2
843 -7.777 | -8 | -7.8 | -7 | -7.7
844 Infinity | Infinity | Infinity | Infinity | Infinity
845 -Infinity | -Infinity | -Infinity | -Infinity | -Infinity
846 NaN | NaN | NaN | NaN | NaN
847 (8 rows)
849 -- the large values fall into the numeric abbreviation code's maximal classes
850 WITH v(x) AS
851 (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('1e340'),('-1e340'),
852 ('inf'),('-inf'),('nan'),
853 ('inf'),('-inf'),('nan'))
854 SELECT substring(x::text, 1, 32)
855 FROM v ORDER BY x;
856 substring
857 ----------------------------------
858 -Infinity
859 -Infinity
860 -1000000000000000000000000000000
861 -7.777
862 -1
863 0
864 1
865 4.2
866 10000000000000000000000000000000
867 Infinity
868 Infinity
869 NaN
870 NaN
871 (13 rows)
873 WITH v(x) AS
874 (VALUES('0'::numeric),('1'),('4.2'),('inf'),('nan'))
875 SELECT x, sqrt(x)
876 FROM v;
877 x | sqrt
878 ----------+-------------------
879 0 | 0.000000000000000
880 1 | 1.000000000000000
881 4.2 | 2.049390153191920
882 Infinity | Infinity
883 NaN | NaN
884 (5 rows)
886 SELECT sqrt('-1'::numeric);
887 ERROR: cannot take square root of a negative number
888 SELECT sqrt('-inf'::numeric);
889 ERROR: cannot take square root of a negative number
890 WITH v(x) AS
891 (VALUES('1'::numeric),('4.2'),('inf'),('nan'))
892 SELECT x,
893 log(x),
894 log10(x),
895 ln(x)
896 FROM v;
897 x | log | log10 | ln
898 ----------+--------------------+--------------------+--------------------
899 1 | 0.0000000000000000 | 0.0000000000000000 | 0.0000000000000000
900 4.2 | 0.6232492903979005 | 0.6232492903979005 | 1.4350845252893226
901 Infinity | Infinity | Infinity | Infinity
902 NaN | NaN | NaN | NaN
903 (4 rows)
905 SELECT ln('0'::numeric);
906 ERROR: cannot take logarithm of zero
907 SELECT ln('-1'::numeric);
908 ERROR: cannot take logarithm of a negative number
909 SELECT ln('-inf'::numeric);
910 ERROR: cannot take logarithm of a negative number
911 WITH v(x) AS
912 (VALUES('2'::numeric),('4.2'),('inf'),('nan'))
913 SELECT x1, x2,
914 log(x1, x2)
915 FROM v AS v1(x1), v AS v2(x2);
916 x1 | x2 | log
917 ----------+----------+--------------------
918 2 | 2 | 1.0000000000000000
919 2 | 4.2 | 2.0703893278913979
920 2 | Infinity | Infinity
921 2 | NaN | NaN
922 4.2 | 2 | 0.4830009440873890
923 4.2 | 4.2 | 1.0000000000000000
924 4.2 | Infinity | Infinity
925 4.2 | NaN | NaN
926 Infinity | 2 | 0
927 Infinity | 4.2 | 0
928 Infinity | Infinity | NaN
929 Infinity | NaN | NaN
930 NaN | 2 | NaN
931 NaN | 4.2 | NaN
932 NaN | Infinity | NaN
933 NaN | NaN | NaN
934 (16 rows)
936 SELECT log('0'::numeric, '10');
937 ERROR: cannot take logarithm of zero
938 SELECT log('10'::numeric, '0');
939 ERROR: cannot take logarithm of zero
940 SELECT log('-inf'::numeric, '10');
941 ERROR: cannot take logarithm of a negative number
942 SELECT log('10'::numeric, '-inf');
943 ERROR: cannot take logarithm of a negative number
944 SELECT log('inf'::numeric, '0');
945 ERROR: cannot take logarithm of zero
946 SELECT log('inf'::numeric, '-inf');
947 ERROR: cannot take logarithm of a negative number
948 SELECT log('-inf'::numeric, 'inf');
949 ERROR: cannot take logarithm of a negative number
950 WITH v(x) AS
951 (VALUES('0'::numeric),('1'),('2'),('4.2'),('inf'),('nan'))
952 SELECT x1, x2,
953 power(x1, x2)
954 FROM v AS v1(x1), v AS v2(x2) WHERE x1 != 0 OR x2 >= 0;
955 x1 | x2 | power
956 ----------+----------+--------------------
957 0 | 0 | 1.0000000000000000
958 0 | 1 | 0.0000000000000000
959 0 | 2 | 0.0000000000000000
960 0 | 4.2 | 0.0000000000000000
961 0 | Infinity | 0
962 0 | NaN | NaN
963 1 | 0 | 1.0000000000000000
964 1 | 1 | 1.0000000000000000
965 1 | 2 | 1.0000000000000000
966 1 | 4.2 | 1.0000000000000000
967 1 | Infinity | 1
968 1 | NaN | 1
969 2 | 0 | 1.0000000000000000
970 2 | 1 | 2.0000000000000000
971 2 | 2 | 4.0000000000000000
972 2 | 4.2 | 18.379173679952560
973 2 | Infinity | Infinity
974 2 | NaN | NaN
975 4.2 | 0 | 1.0000000000000000
976 4.2 | 1 | 4.2000000000000000
977 4.2 | 2 | 17.640000000000000
978 4.2 | 4.2 | 414.61691860129675
979 4.2 | Infinity | Infinity
980 4.2 | NaN | NaN
981 Infinity | 0 | 1
982 Infinity | 1 | Infinity
983 Infinity | 2 | Infinity
984 Infinity | 4.2 | Infinity
985 Infinity | Infinity | Infinity
986 Infinity | NaN | NaN
987 NaN | 0 | 1
988 NaN | 1 | NaN
989 NaN | 2 | NaN
990 NaN | 4.2 | NaN
991 NaN | Infinity | NaN
992 NaN | NaN | NaN
993 (36 rows)
995 SELECT power('0'::numeric, '-1');
996 ERROR: zero raised to a negative power is undefined
997 SELECT power('0'::numeric, '-inf');
998 ERROR: zero raised to a negative power is undefined
999 SELECT power('-1'::numeric, 'inf');
1000 power
1001 -------
1002 1
1003 (1 row)
1005 SELECT power('-2'::numeric, '3');
1006 power
1007 ---------------------
1008 -8.0000000000000000
1009 (1 row)
1011 SELECT power('-2'::numeric, '3.3');
1012 ERROR: a negative number raised to a non-integer power yields a complex result
1013 SELECT power('-2'::numeric, '-1');
1014 power
1015 ---------------------
1016 -0.5000000000000000
1017 (1 row)
1019 SELECT power('-2'::numeric, '-1.5');
1020 ERROR: a negative number raised to a non-integer power yields a complex result
1021 SELECT power('-2'::numeric, 'inf');
1022 power
1023 ----------
1024 Infinity
1025 (1 row)
1027 SELECT power('-2'::numeric, '-inf');
1028 power
1029 -------
1030 0
1031 (1 row)
1033 SELECT power('inf'::numeric, '-2');
1034 power
1035 -------
1036 0
1037 (1 row)
1039 SELECT power('inf'::numeric, '-inf');
1040 power
1041 -------
1042 0
1043 (1 row)
1045 SELECT power('-inf'::numeric, '2');
1046 power
1047 ----------
1048 Infinity
1049 (1 row)
1051 SELECT power('-inf'::numeric, '3');
1052 power
1053 -----------
1054 -Infinity
1055 (1 row)
1057 SELECT power('-inf'::numeric, '4.5');
1058 ERROR: a negative number raised to a non-integer power yields a complex result
1059 SELECT power('-inf'::numeric, '-2');
1060 power
1061 -------
1062 0
1063 (1 row)
1065 SELECT power('-inf'::numeric, '-3');
1066 power
1067 -------
1068 0
1069 (1 row)
1071 SELECT power('-inf'::numeric, '0');
1072 power
1073 -------
1074 1
1075 (1 row)
1077 SELECT power('-inf'::numeric, 'inf');
1078 power
1079 ----------
1080 Infinity
1081 (1 row)
1083 SELECT power('-inf'::numeric, '-inf');
1084 power
1085 -------
1086 0
1087 (1 row)
1089 -- ******************************
1090 -- * miscellaneous checks for things that have been broken in the past...
1091 -- ******************************
1092 -- numeric AVG used to fail on some platforms
1093 SELECT AVG(val) FROM num_data;
1094 avg
1095 ------------------------
1096 -13430913.592242320700
1097 (1 row)
1099 SELECT MAX(val) FROM num_data;
1100 max
1101 --------------------
1102 7799461.4119000000
1103 (1 row)
1105 SELECT MIN(val) FROM num_data;
1106 min
1107 ----------------------
1108 -83028485.0000000000
1109 (1 row)
1111 SELECT STDDEV(val) FROM num_data;
1112 stddev
1113 -------------------------------
1114 27791203.28758835329805617386
1115 (1 row)
1117 SELECT VARIANCE(val) FROM num_data;
1118 variance
1119 --------------------------------------
1120 772350980172061.69659105821915863601
1121 (1 row)
1123 -- Check for appropriate rounding and overflow
1124 CREATE TABLE fract_only (id int, val numeric(4,4));
1125 INSERT INTO fract_only VALUES (1, '0.0');
1126 INSERT INTO fract_only VALUES (2, '0.1');
1127 INSERT INTO fract_only VALUES (3, '1.0'); -- should fail
1128 ERROR: numeric field overflow
1129 DETAIL: A field with precision 4, scale 4 must round to an absolute value less than 1.
1130 INSERT INTO fract_only VALUES (4, '-0.9999');
1131 INSERT INTO fract_only VALUES (5, '0.99994');
1132 INSERT INTO fract_only VALUES (6, '0.99995'); -- should fail
1133 ERROR: numeric field overflow
1134 DETAIL: A field with precision 4, scale 4 must round to an absolute value less than 1.
1135 INSERT INTO fract_only VALUES (7, '0.00001');
1136 INSERT INTO fract_only VALUES (8, '0.00017');
1137 INSERT INTO fract_only VALUES (9, 'NaN');
1138 INSERT INTO fract_only VALUES (10, 'Inf'); -- should fail
1139 ERROR: numeric field overflow
1140 DETAIL: A field with precision 4, scale 4 cannot hold an infinite value.
1141 INSERT INTO fract_only VALUES (11, '-Inf'); -- should fail
1142 ERROR: numeric field overflow
1143 DETAIL: A field with precision 4, scale 4 cannot hold an infinite value.
1144 SELECT * FROM fract_only;
1145 id | val
1146 ----+---------
1147 1 | 0.0000
1148 2 | 0.1000
1149 4 | -0.9999
1150 5 | 0.9999
1151 7 | 0.0000
1152 8 | 0.0002
1153 9 | NaN
1154 (7 rows)
1156 DROP TABLE fract_only;
1157 -- Check conversion to integers
1158 SELECT (-9223372036854775808.5)::int8; -- should fail
1159 ERROR: bigint out of range
1160 SELECT (-9223372036854775808.4)::int8; -- ok
1161 int8
1162 ----------------------
1163 -9223372036854775808
1164 (1 row)
1166 SELECT 9223372036854775807.4::int8; -- ok
1167 int8
1168 ---------------------
1169 9223372036854775807
1170 (1 row)
1172 SELECT 9223372036854775807.5::int8; -- should fail
1173 ERROR: bigint out of range
1174 SELECT (-2147483648.5)::int4; -- should fail
1175 ERROR: integer out of range
1176 SELECT (-2147483648.4)::int4; -- ok
1177 int4
1178 -------------
1179 -2147483648
1180 (1 row)
1182 SELECT 2147483647.4::int4; -- ok
1183 int4
1184 ------------
1185 2147483647
1186 (1 row)
1188 SELECT 2147483647.5::int4; -- should fail
1189 ERROR: integer out of range
1190 SELECT (-32768.5)::int2; -- should fail
1191 ERROR: smallint out of range
1192 SELECT (-32768.4)::int2; -- ok
1193 int2
1194 --------
1195 -32768
1196 (1 row)
1198 SELECT 32767.4::int2; -- ok
1199 int2
1200 -------
1201 32767
1202 (1 row)
1204 SELECT 32767.5::int2; -- should fail
1205 ERROR: smallint out of range
1206 -- Check inf/nan conversion behavior
1207 SELECT 'NaN'::float8::numeric;
1208 numeric
1209 ---------
1210 NaN
1211 (1 row)
1213 SELECT 'Infinity'::float8::numeric;
1214 numeric
1215 ----------
1216 Infinity
1217 (1 row)
1219 SELECT '-Infinity'::float8::numeric;
1220 numeric
1221 -----------
1222 -Infinity
1223 (1 row)
1225 SELECT 'NaN'::numeric::float8;
1226 float8
1227 --------
1228 NaN
1229 (1 row)
1231 SELECT 'Infinity'::numeric::float8;
1232 float8
1233 ----------
1234 Infinity
1235 (1 row)
1237 SELECT '-Infinity'::numeric::float8;
1238 float8
1239 -----------
1240 -Infinity
1241 (1 row)
1243 SELECT 'NaN'::float4::numeric;
1244 numeric
1245 ---------
1246 NaN
1247 (1 row)
1249 SELECT 'Infinity'::float4::numeric;
1250 numeric
1251 ----------
1252 Infinity
1253 (1 row)
1255 SELECT '-Infinity'::float4::numeric;
1256 numeric
1257 -----------
1258 -Infinity
1259 (1 row)
1261 SELECT 'NaN'::numeric::float4;
1262 float4
1263 --------
1264 NaN
1265 (1 row)
1267 SELECT 'Infinity'::numeric::float4;
1268 float4
1269 ----------
1270 Infinity
1271 (1 row)
1273 SELECT '-Infinity'::numeric::float4;
1274 float4
1275 -----------
1276 -Infinity
1277 (1 row)
1279 SELECT '42'::int2::numeric;
1280 numeric
1281 ---------
1282 42
1283 (1 row)
1285 SELECT 'NaN'::numeric::int2;
1286 ERROR: cannot convert NaN to smallint
1287 SELECT 'Infinity'::numeric::int2;
1288 ERROR: cannot convert infinity to smallint
1289 SELECT '-Infinity'::numeric::int2;
1290 ERROR: cannot convert infinity to smallint
1291 SELECT 'NaN'::numeric::int4;
1292 ERROR: cannot convert NaN to integer
1293 SELECT 'Infinity'::numeric::int4;
1294 ERROR: cannot convert infinity to integer
1295 SELECT '-Infinity'::numeric::int4;
1296 ERROR: cannot convert infinity to integer
1297 SELECT 'NaN'::numeric::int8;
1298 ERROR: cannot convert NaN to bigint
1299 SELECT 'Infinity'::numeric::int8;
1300 ERROR: cannot convert infinity to bigint
1301 SELECT '-Infinity'::numeric::int8;
1302 ERROR: cannot convert infinity to bigint
1303 -- Simple check that ceil(), floor(), and round() work correctly
1304 CREATE TABLE ceil_floor_round (a numeric);
1305 INSERT INTO ceil_floor_round VALUES ('-5.5');
1306 INSERT INTO ceil_floor_round VALUES ('-5.499999');
1307 INSERT INTO ceil_floor_round VALUES ('9.5');
1308 INSERT INTO ceil_floor_round VALUES ('9.4999999');
1309 INSERT INTO ceil_floor_round VALUES ('0.0');
1310 INSERT INTO ceil_floor_round VALUES ('0.0000001');
1311 INSERT INTO ceil_floor_round VALUES ('-0.000001');
1312 SELECT a, ceil(a), ceiling(a), floor(a), round(a) FROM ceil_floor_round;
1313 a | ceil | ceiling | floor | round
1314 -----------+------+---------+-------+-------
1315 -5.5 | -5 | -5 | -6 | -6
1316 -5.499999 | -5 | -5 | -6 | -5
1317 9.5 | 10 | 10 | 9 | 10
1318 9.4999999 | 10 | 10 | 9 | 9
1319 0.0 | 0 | 0 | 0 | 0
1320 0.0000001 | 1 | 1 | 0 | 0
1321 -0.000001 | 0 | 0 | -1 | 0
1322 (7 rows)
1324 DROP TABLE ceil_floor_round;
1325 -- Check rounding, it should round ties away from zero.
1326 SELECT i as pow,
1327 round((-2.5 * 10 ^ i)::numeric, -i),
1328 round((-1.5 * 10 ^ i)::numeric, -i),
1329 round((-0.5 * 10 ^ i)::numeric, -i),
1330 round((0.5 * 10 ^ i)::numeric, -i),
1331 round((1.5 * 10 ^ i)::numeric, -i),
1332 round((2.5 * 10 ^ i)::numeric, -i)
1333 FROM generate_series(-5,5) AS t(i);
1334 pow | round | round | round | round | round | round
1335 -----+----------+----------+----------+---------+---------+---------
1336 -5 | -0.00003 | -0.00002 | -0.00001 | 0.00001 | 0.00002 | 0.00003
1337 -4 | -0.0003 | -0.0002 | -0.0001 | 0.0001 | 0.0002 | 0.0003
1338 -3 | -0.003 | -0.002 | -0.001 | 0.001 | 0.002 | 0.003
1339 -2 | -0.03 | -0.02 | -0.01 | 0.01 | 0.02 | 0.03
1340 -1 | -0.3 | -0.2 | -0.1 | 0.1 | 0.2 | 0.3
1341 0 | -3 | -2 | -1 | 1 | 2 | 3
1342 1 | -30 | -20 | -10 | 10 | 20 | 30
1343 2 | -300 | -200 | -100 | 100 | 200 | 300
1344 3 | -3000 | -2000 | -1000 | 1000 | 2000 | 3000
1345 4 | -30000 | -20000 | -10000 | 10000 | 20000 | 30000
1346 5 | -300000 | -200000 | -100000 | 100000 | 200000 | 300000
1347 (11 rows)
1349 -- Check limits of rounding before the decimal point
1350 SELECT round(4.4e131071, -131071) = 4e131071;
1351 ?column?
1352 ----------
1353 t
1354 (1 row)
1356 SELECT round(4.5e131071, -131071) = 5e131071;
1357 ?column?
1358 ----------
1359 t
1360 (1 row)
1362 SELECT round(4.5e131071, -131072); -- loses all digits
1363 round
1364 -------
1365 0
1366 (1 row)
1368 SELECT round(5.5e131071, -131072); -- rounds up and overflows
1369 ERROR: value overflows numeric format
1370 SELECT round(5.5e131071, -131073); -- loses all digits
1371 round
1372 -------
1373 0
1374 (1 row)
1376 SELECT round(5.5e131071, -1000000); -- loses all digits
1377 round
1378 -------
1379 0
1380 (1 row)
1382 -- Check limits of rounding after the decimal point
1383 SELECT round(5e-16383, 1000000) = 5e-16383;
1384 ?column?
1385 ----------
1386 t
1387 (1 row)
1389 SELECT round(5e-16383, 16383) = 5e-16383;
1390 ?column?
1391 ----------
1392 t
1393 (1 row)
1395 SELECT round(5e-16383, 16382) = 1e-16382;
1396 ?column?
1397 ----------
1398 t
1399 (1 row)
1401 SELECT round(5e-16383, 16381) = 0;
1402 ?column?
1403 ----------
1404 t
1405 (1 row)
1407 -- Check limits of trunc() before the decimal point
1408 SELECT trunc(9.9e131071, -131071) = 9e131071;
1409 ?column?
1410 ----------
1411 t
1412 (1 row)
1414 SELECT trunc(9.9e131071, -131072); -- loses all digits
1415 trunc
1416 -------
1417 0
1418 (1 row)
1420 SELECT trunc(9.9e131071, -131073); -- loses all digits
1421 trunc
1422 -------
1423 0
1424 (1 row)
1426 SELECT trunc(9.9e131071, -1000000); -- loses all digits
1427 trunc
1428 -------
1429 0
1430 (1 row)
1432 -- Check limits of trunc() after the decimal point
1433 SELECT trunc(5e-16383, 1000000) = 5e-16383;
1434 ?column?
1435 ----------
1436 t
1437 (1 row)
1439 SELECT trunc(5e-16383, 16383) = 5e-16383;
1440 ?column?
1441 ----------
1442 t
1443 (1 row)
1445 SELECT trunc(5e-16383, 16382) = 0;
1446 ?column?
1447 ----------
1448 t
1449 (1 row)
1451 -- Testing for width_bucket(). For convenience, we test both the
1452 -- numeric and float8 versions of the function in this file.
1453 -- errors
1454 SELECT width_bucket(5.0, 3.0, 4.0, 0);
1455 ERROR: count must be greater than zero
1456 SELECT width_bucket(5.0, 3.0, 4.0, -5);
1457 ERROR: count must be greater than zero
1458 SELECT width_bucket(3.5, 3.0, 3.0, 888);
1459 ERROR: lower bound cannot equal upper bound
1460 SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, 0);
1461 ERROR: count must be greater than zero
1462 SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, -5);
1463 ERROR: count must be greater than zero
1464 SELECT width_bucket(3.5::float8, 3.0::float8, 3.0::float8, 888);
1465 ERROR: lower bound cannot equal upper bound
1466 SELECT width_bucket('NaN', 3.0, 4.0, 888);
1467 ERROR: operand, lower bound, and upper bound cannot be NaN
1468 SELECT width_bucket(0::float8, 'NaN', 4.0::float8, 888);
1469 ERROR: operand, lower bound, and upper bound cannot be NaN
1470 SELECT width_bucket(2.0, 3.0, '-inf', 888);
1471 ERROR: lower and upper bounds must be finite
1472 SELECT width_bucket(0::float8, '-inf', 4.0::float8, 888);
1473 ERROR: lower and upper bounds must be finite
1474 -- normal operation
1475 CREATE TABLE width_bucket_test (operand_num numeric, operand_f8 float8);
1476 COPY width_bucket_test (operand_num) FROM stdin;
1477 UPDATE width_bucket_test SET operand_f8 = operand_num::float8;
1478 SELECT
1479 operand_num,
1480 width_bucket(operand_num, 0, 10, 5) AS wb_1,
1481 width_bucket(operand_f8, 0, 10, 5) AS wb_1f,
1482 width_bucket(operand_num, 10, 0, 5) AS wb_2,
1483 width_bucket(operand_f8, 10, 0, 5) AS wb_2f,
1484 width_bucket(operand_num, 2, 8, 4) AS wb_3,
1485 width_bucket(operand_f8, 2, 8, 4) AS wb_3f,
1486 width_bucket(operand_num, 5.0, 5.5, 20) AS wb_4,
1487 width_bucket(operand_f8, 5.0, 5.5, 20) AS wb_4f,
1488 width_bucket(operand_num, -25, 25, 10) AS wb_5,
1489 width_bucket(operand_f8, -25, 25, 10) AS wb_5f
1490 FROM width_bucket_test;
1491 operand_num | wb_1 | wb_1f | wb_2 | wb_2f | wb_3 | wb_3f | wb_4 | wb_4f | wb_5 | wb_5f
1492 ------------------+------+-------+------+-------+------+-------+------+-------+------+-------
1493 -5.2 | 0 | 0 | 6 | 6 | 0 | 0 | 0 | 0 | 4 | 4
1494 -0.0000000001 | 0 | 0 | 6 | 6 | 0 | 0 | 0 | 0 | 5 | 5
1495 0.000000000001 | 1 | 1 | 5 | 5 | 0 | 0 | 0 | 0 | 6 | 6
1496 1 | 1 | 1 | 5 | 5 | 0 | 0 | 0 | 0 | 6 | 6
1497 1.99999999999999 | 1 | 1 | 5 | 5 | 0 | 0 | 0 | 0 | 6 | 6
1498 2 | 2 | 2 | 5 | 5 | 1 | 1 | 0 | 0 | 6 | 6
1499 2.00000000000001 | 2 | 2 | 4 | 4 | 1 | 1 | 0 | 0 | 6 | 6
1500 3 | 2 | 2 | 4 | 4 | 1 | 1 | 0 | 0 | 6 | 6
1501 4 | 3 | 3 | 4 | 4 | 2 | 2 | 0 | 0 | 6 | 6
1502 4.5 | 3 | 3 | 3 | 3 | 2 | 2 | 0 | 0 | 6 | 6
1503 5 | 3 | 3 | 3 | 3 | 3 | 3 | 1 | 1 | 7 | 7
1504 5.5 | 3 | 3 | 3 | 3 | 3 | 3 | 21 | 21 | 7 | 7
1505 6 | 4 | 4 | 3 | 3 | 3 | 3 | 21 | 21 | 7 | 7
1506 7 | 4 | 4 | 2 | 2 | 4 | 4 | 21 | 21 | 7 | 7
1507 8 | 5 | 5 | 2 | 2 | 5 | 5 | 21 | 21 | 7 | 7
1508 9 | 5 | 5 | 1 | 1 | 5 | 5 | 21 | 21 | 7 | 7
1509 9.99999999999999 | 5 | 5 | 1 | 1 | 5 | 5 | 21 | 21 | 7 | 7
1510 10 | 6 | 6 | 1 | 1 | 5 | 5 | 21 | 21 | 8 | 8
1511 10.0000000000001 | 6 | 6 | 0 | 0 | 5 | 5 | 21 | 21 | 8 | 8
1512 (19 rows)
1514 -- Check positive and negative infinity: we require
1515 -- finite bucket bounds, but allow an infinite operand
1516 SELECT width_bucket(0.0::numeric, 'Infinity'::numeric, 5, 10); -- error
1517 ERROR: lower and upper bounds must be finite
1518 SELECT width_bucket(0.0::numeric, 5, '-Infinity'::numeric, 20); -- error
1519 ERROR: lower and upper bounds must be finite
1520 SELECT width_bucket('Infinity'::numeric, 1, 10, 10),
1521 width_bucket('-Infinity'::numeric, 1, 10, 10);
1522 width_bucket | width_bucket
1523 --------------+--------------
1524 11 | 0
1525 (1 row)
1527 SELECT width_bucket(0.0::float8, 'Infinity'::float8, 5, 10); -- error
1528 ERROR: lower and upper bounds must be finite
1529 SELECT width_bucket(0.0::float8, 5, '-Infinity'::float8, 20); -- error
1530 ERROR: lower and upper bounds must be finite
1531 SELECT width_bucket('Infinity'::float8, 1, 10, 10),
1532 width_bucket('-Infinity'::float8, 1, 10, 10);
1533 width_bucket | width_bucket
1534 --------------+--------------
1535 11 | 0
1536 (1 row)
1538 DROP TABLE width_bucket_test;
1539 -- Simple test for roundoff error when results should be exact
1540 SELECT x, width_bucket(x::float8, 10, 100, 9) as flt,
1541 width_bucket(x::numeric, 10, 100, 9) as num
1542 FROM generate_series(0, 110, 10) x;
1543 x | flt | num
1544 -----+-----+-----
1545 0 | 0 | 0
1546 10 | 1 | 1
1547 20 | 2 | 2
1548 30 | 3 | 3
1549 40 | 4 | 4
1550 50 | 5 | 5
1551 60 | 6 | 6
1552 70 | 7 | 7
1553 80 | 8 | 8
1554 90 | 9 | 9
1555 100 | 10 | 10
1556 110 | 10 | 10
1557 (12 rows)
1559 SELECT x, width_bucket(x::float8, 100, 10, 9) as flt,
1560 width_bucket(x::numeric, 100, 10, 9) as num
1561 FROM generate_series(0, 110, 10) x;
1562 x | flt | num
1563 -----+-----+-----
1564 0 | 10 | 10
1565 10 | 10 | 10
1566 20 | 9 | 9
1567 30 | 8 | 8
1568 40 | 7 | 7
1569 50 | 6 | 6
1570 60 | 5 | 5
1571 70 | 4 | 4
1572 80 | 3 | 3
1573 90 | 2 | 2
1574 100 | 1 | 1
1575 110 | 0 | 0
1576 (12 rows)
1578 -- Another roundoff-error hazard
1579 SELECT width_bucket(0, -1e100::numeric, 1, 10);
1580 width_bucket
1581 --------------
1582 10
1583 (1 row)
1585 SELECT width_bucket(0, -1e100::float8, 1, 10);
1586 width_bucket
1587 --------------
1588 10
1589 (1 row)
1591 SELECT width_bucket(1, 1e100::numeric, 0, 10);
1592 width_bucket
1593 --------------
1594 10
1595 (1 row)
1597 SELECT width_bucket(1, 1e100::float8, 0, 10);
1598 width_bucket
1599 --------------
1600 10
1601 (1 row)
1603 -- Check cases that could trigger overflow or underflow within the calculation
1604 SELECT oper, low, high, cnt, width_bucket(oper, low, high, cnt)
1605 FROM
1606 (SELECT 1.797e+308::float8 AS big, 5e-324::float8 AS tiny) as v,
1607 LATERAL (VALUES
1608 (10.5::float8, -big, big, 1),
1609 (10.5::float8, -big, big, 2),
1610 (10.5::float8, -big, big, 3),
1611 (big / 4, -big / 2, big / 2, 10),
1612 (10.5::float8, big, -big, 1),
1613 (10.5::float8, big, -big, 2),
1614 (10.5::float8, big, -big, 3),
1615 (big / 4, big / 2, -big / 2, 10),
1616 (0, 0, tiny, 4),
1617 (tiny, 0, tiny, 4),
1618 (0, 0, 1, 2147483647),
1619 (1, 1, 0, 2147483647)
1620 ) as sample(oper, low, high, cnt);
1621 oper | low | high | cnt | width_bucket
1622 -------------+-------------+-------------+------------+--------------
1623 10.5 | -1.797e+308 | 1.797e+308 | 1 | 1
1624 10.5 | -1.797e+308 | 1.797e+308 | 2 | 2
1625 10.5 | -1.797e+308 | 1.797e+308 | 3 | 2
1626 4.4925e+307 | -8.985e+307 | 8.985e+307 | 10 | 8
1627 10.5 | 1.797e+308 | -1.797e+308 | 1 | 1
1628 10.5 | 1.797e+308 | -1.797e+308 | 2 | 2
1629 10.5 | 1.797e+308 | -1.797e+308 | 3 | 2
1630 4.4925e+307 | 8.985e+307 | -8.985e+307 | 10 | 3
1631 0 | 0 | 5e-324 | 4 | 1
1632 5e-324 | 0 | 5e-324 | 4 | 5
1633 0 | 0 | 1 | 2147483647 | 1
1634 1 | 1 | 0 | 2147483647 | 1
1635 (12 rows)
1637 -- These fail because the result would be out of int32 range:
1638 SELECT width_bucket(1::float8, 0, 1, 2147483647);
1639 ERROR: integer out of range
1640 SELECT width_bucket(0::float8, 1, 0, 2147483647);
1641 ERROR: integer out of range
1642 --
1643 -- TO_CHAR()
1644 --
1645 SELECT to_char(val, '9G999G999G999G999G999')
1646 FROM num_data;
1647 to_char
1648 ------------------------
1649 0
1650 0
1651 -34,338,492
1652 4
1653 7,799,461
1654 16,397
1655 93,902
1656 -83,028,485
1657 74,881
1658 -24,926,804
1659 (10 rows)
1661 SELECT to_char(val, '9G999G999G999G999G999D999G999G999G999G999')
1662 FROM num_data;
1663 to_char
1664 --------------------------------------------
1665 .000,000,000,000,000
1666 .000,000,000,000,000
1667 -34,338,492.215,397,047,000,000
1668 4.310,000,000,000,000
1669 7,799,461.411,900,000,000,000
1670 16,397.038,491,000,000,000
1671 93,901.577,630,260,000,000
1672 -83,028,485.000,000,000,000,000
1673 74,881.000,000,000,000,000
1674 -24,926,804.045,047,420,000,000
1675 (10 rows)
1677 SELECT to_char(val, '9999999999999999.999999999999999PR')
1678 FROM num_data;
1679 to_char
1680 ------------------------------------
1681 .000000000000000
1682 .000000000000000
1683 <34338492.215397047000000>
1684 4.310000000000000
1685 7799461.411900000000000
1686 16397.038491000000000
1687 93901.577630260000000
1688 <83028485.000000000000000>
1689 74881.000000000000000
1690 <24926804.045047420000000>
1691 (10 rows)
1693 SELECT to_char(val, '9999999999999999.999999999999999S')
1694 FROM num_data;
1695 to_char
1696 -----------------------------------
1697 .000000000000000+
1698 .000000000000000+
1699 34338492.215397047000000-
1700 4.310000000000000+
1701 7799461.411900000000000+
1702 16397.038491000000000+
1703 93901.577630260000000+
1704 83028485.000000000000000-
1705 74881.000000000000000+
1706 24926804.045047420000000-
1707 (10 rows)
1709 SELECT to_char(val, 'MI9999999999999999.999999999999999') FROM num_data;
1710 to_char
1711 -----------------------------------
1712 .000000000000000
1713 .000000000000000
1714 - 34338492.215397047000000
1715 4.310000000000000
1716 7799461.411900000000000
1717 16397.038491000000000
1718 93901.577630260000000
1719 - 83028485.000000000000000
1720 74881.000000000000000
1721 - 24926804.045047420000000
1722 (10 rows)
1724 SELECT to_char(val, 'FMS9999999999999999.999999999999999') FROM num_data;
1725 to_char
1726 ---------------------
1727 +0.
1728 +0.
1729 -34338492.215397047
1730 +4.31
1731 +7799461.4119
1732 +16397.038491
1733 +93901.57763026
1734 -83028485.
1735 +74881.
1736 -24926804.04504742
1737 (10 rows)
1739 SELECT to_char(val, 'FM9999999999999999.999999999999999THPR') FROM num_data;
1740 to_char
1741 ----------------------
1742 0.
1743 0.
1744 <34338492.215397047>
1745 4.31
1746 7799461.4119
1747 16397.038491
1748 93901.57763026
1749 <83028485.>
1750 74881.
1751 <24926804.04504742>
1752 (10 rows)
1754 SELECT to_char(val, 'SG9999999999999999.999999999999999th') FROM num_data;
1755 to_char
1756 -----------------------------------
1757 + .000000000000000
1758 + .000000000000000
1759 - 34338492.215397047000000
1760 + 4.310000000000000
1761 + 7799461.411900000000000
1762 + 16397.038491000000000
1763 + 93901.577630260000000
1764 - 83028485.000000000000000
1765 + 74881.000000000000000
1766 - 24926804.045047420000000
1767 (10 rows)
1769 SELECT to_char(val, '0999999999999999.999999999999999') FROM num_data;
1770 to_char
1771 -----------------------------------
1772 0000000000000000.000000000000000
1773 0000000000000000.000000000000000
1774 -0000000034338492.215397047000000
1775 0000000000000004.310000000000000
1776 0000000007799461.411900000000000
1777 0000000000016397.038491000000000
1778 0000000000093901.577630260000000
1779 -0000000083028485.000000000000000
1780 0000000000074881.000000000000000
1781 -0000000024926804.045047420000000
1782 (10 rows)
1784 SELECT to_char(val, 'S0999999999999999.999999999999999') FROM num_data;
1785 to_char
1786 -----------------------------------
1787 +0000000000000000.000000000000000
1788 +0000000000000000.000000000000000
1789 -0000000034338492.215397047000000
1790 +0000000000000004.310000000000000
1791 +0000000007799461.411900000000000
1792 +0000000000016397.038491000000000
1793 +0000000000093901.577630260000000
1794 -0000000083028485.000000000000000
1795 +0000000000074881.000000000000000
1796 -0000000024926804.045047420000000
1797 (10 rows)
1799 SELECT to_char(val, 'FM0999999999999999.999999999999999') FROM num_data;
1800 to_char
1801 -----------------------------
1802 0000000000000000.
1803 0000000000000000.
1804 -0000000034338492.215397047
1805 0000000000000004.31
1806 0000000007799461.4119
1807 0000000000016397.038491
1808 0000000000093901.57763026
1809 -0000000083028485.
1810 0000000000074881.
1811 -0000000024926804.04504742
1812 (10 rows)
1814 SELECT to_char(val, 'FM9999999999999999.099999999999999') FROM num_data;
1815 to_char
1816 ---------------------
1817 .0
1818 .0
1819 -34338492.215397047
1820 4.31
1821 7799461.4119
1822 16397.038491
1823 93901.57763026
1824 -83028485.0
1825 74881.0
1826 -24926804.04504742
1827 (10 rows)
1829 SELECT to_char(val, 'FM9999999999990999.990999999999999') FROM num_data;
1830 to_char
1831 ---------------------
1832 0000.000
1833 0000.000
1834 -34338492.215397047
1835 0004.310
1836 7799461.4119
1837 16397.038491
1838 93901.57763026
1839 -83028485.000
1840 74881.000
1841 -24926804.04504742
1842 (10 rows)
1844 SELECT to_char(val, 'FM0999999999999999.999909999999999') FROM num_data;
1845 to_char
1846 -----------------------------
1847 0000000000000000.00000
1848 0000000000000000.00000
1849 -0000000034338492.215397047
1850 0000000000000004.31000
1851 0000000007799461.41190
1852 0000000000016397.038491
1853 0000000000093901.57763026
1854 -0000000083028485.00000
1855 0000000000074881.00000
1856 -0000000024926804.04504742
1857 (10 rows)
1859 SELECT to_char(val, 'FM9999999990999999.099999999999999') FROM num_data;
1860 to_char
1861 ---------------------
1862 0000000.0
1863 0000000.0
1864 -34338492.215397047
1865 0000004.31
1866 7799461.4119
1867 0016397.038491
1868 0093901.57763026
1869 -83028485.0
1870 0074881.0
1871 -24926804.04504742
1872 (10 rows)
1874 SELECT to_char(val, 'L9999999999999999.099999999999999') FROM num_data;
1875 to_char
1876 ------------------------------------
1877 .000000000000000
1878 .000000000000000
1879 -34338492.215397047000000
1880 4.310000000000000
1881 7799461.411900000000000
1882 16397.038491000000000
1883 93901.577630260000000
1884 -83028485.000000000000000
1885 74881.000000000000000
1886 -24926804.045047420000000
1887 (10 rows)
1889 SELECT to_char(val, 'FM9999999999999999.99999999999999') FROM num_data;
1890 to_char
1891 ---------------------
1892 0.
1893 0.
1894 -34338492.215397047
1895 4.31
1896 7799461.4119
1897 16397.038491
1898 93901.57763026
1899 -83028485.
1900 74881.
1901 -24926804.04504742
1902 (10 rows)
1904 SELECT to_char(val, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data;
1905 to_char
1906 -----------------------------------------------------------------------
1907 +. 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1908 +. 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1909 -3 4 3 3 8 4 9 2 . 2 1 5 3 9 7 0 4 7 0 0 0 0 0 0 0 0
1910 +4 . 3 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1911 +7 7 9 9 4 6 1 . 4 1 1 9 0 0 0 0 0 0 0 0 0 0 0 0 0
1912 +1 6 3 9 7 . 0 3 8 4 9 1 0 0 0 0 0 0 0 0 0 0 0
1913 +9 3 9 0 1 . 5 7 7 6 3 0 2 6 0 0 0 0 0 0 0 0 0
1914 -8 3 0 2 8 4 8 5 . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1915 +7 4 8 8 1 . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1916 -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2 0 0 0 0 0 0 0 0 0
1917 (10 rows)
1919 SELECT to_char(val, 'FMS 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9') FROM num_data;
1920 to_char
1921 -------------------------------------------------------
1922 +0 .
1923 +0 .
1924 -3 4 3 3 8 4 9 2 . 2 1 5 3 9 7 0 4 7
1925 +4 . 3 1
1926 +7 7 9 9 4 6 1 . 4 1 1 9
1927 +1 6 3 9 7 . 0 3 8 4 9 1
1928 +9 3 9 0 1 . 5 7 7 6 3 0 2 6
1929 -8 3 0 2 8 4 8 5 .
1930 +7 4 8 8 1 .
1931 -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2
1932 (10 rows)
1934 SELECT to_char(val, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data;
1935 to_char
1936 -----------------------------------------------------------
1937 text 9999 "text between quote marks" 0
1938 text 9999 "text between quote marks" 0
1939 text -3 9999 433 "text between quote marks" 8492
1940 text 9999 "text between quote marks" 4
1941 text 9999 779 "text between quote marks" 9461
1942 text 9999 1 "text between quote marks" 6397
1943 text 9999 9 "text between quote marks" 3902
1944 text -8 9999 302 "text between quote marks" 8485
1945 text 9999 7 "text between quote marks" 4881
1946 text -2 9999 492 "text between quote marks" 6804
1947 (10 rows)
1949 SELECT to_char(val, '999999SG9999999999') FROM num_data;
1950 to_char
1951 -------------------
1952 + 0
1953 + 0
1954 - 34338492
1955 + 4
1956 + 7799461
1957 + 16397
1958 + 93902
1959 - 83028485
1960 + 74881
1961 - 24926804
1962 (10 rows)
1964 SELECT to_char(val, 'FM9999999999999999.999999999999999') FROM num_data;
1965 to_char
1966 ---------------------
1967 0.
1968 0.
1969 -34338492.215397047
1970 4.31
1971 7799461.4119
1972 16397.038491
1973 93901.57763026
1974 -83028485.
1975 74881.
1976 -24926804.04504742
1977 (10 rows)
1979 SELECT to_char(val, '9.999EEEE') FROM num_data;
1980 to_char
1981 ------------
1982 0.000e+00
1983 0.000e+00
1984 -3.434e+07
1985 4.310e+00
1986 7.799e+06
1987 1.640e+04
1988 9.390e+04
1989 -8.303e+07
1990 7.488e+04
1991 -2.493e+07
1992 (10 rows)
1994 SELECT to_char(val, 'FMRN') FROM num_data;
1995 to_char
1996 -----------------
1997 ###############
1998 ###############
1999 ###############
2000 IV
2001 ###############
2002 ###############
2003 ###############
2004 ###############
2005 ###############
2006 ###############
2007 (10 rows)
2009 WITH v(val) AS
2010 (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan'))
2011 SELECT val,
2012 to_char(val, '9.999EEEE') as numeric,
2013 to_char(val::float8, '9.999EEEE') as float8,
2014 to_char(val::float4, '9.999EEEE') as float4
2015 FROM v;
2016 val | numeric | float8 | float4
2017 ------------+------------+------------+------------
2018 0 | 0.000e+00 | 0.000e+00 | 0.000e+00
2019 -4.2 | -4.200e+00 | -4.200e+00 | -4.200e+00
2020 4200000000 | 4.200e+09 | 4.200e+09 | 4.200e+09
2021 0.000012 | 1.200e-05 | 1.200e-05 | 1.200e-05
2022 Infinity | #.####### | #.####### | #.#######
2023 -Infinity | #.####### | #.####### | #.#######
2024 NaN | #.####### | #.####### | #.#######
2025 (7 rows)
2027 WITH v(exp) AS
2028 (VALUES(-16379),(-16378),(-1234),(-789),(-45),(-5),(-4),(-3),(-2),(-1),(0),
2029 (1),(2),(3),(4),(5),(38),(275),(2345),(45678),(131070),(131071))
2030 SELECT exp,
2031 to_char(('1.2345e'||exp)::numeric, '9.999EEEE') as numeric
2032 FROM v;
2033 exp | numeric
2034 --------+----------------
2035 -16379 | 1.235e-16379
2036 -16378 | 1.235e-16378
2037 -1234 | 1.235e-1234
2038 -789 | 1.235e-789
2039 -45 | 1.235e-45
2040 -5 | 1.235e-05
2041 -4 | 1.235e-04
2042 -3 | 1.235e-03
2043 -2 | 1.235e-02
2044 -1 | 1.235e-01
2045 0 | 1.235e+00
2046 1 | 1.235e+01
2047 2 | 1.235e+02
2048 3 | 1.235e+03
2049 4 | 1.235e+04
2050 5 | 1.235e+05
2051 38 | 1.235e+38
2052 275 | 1.235e+275
2053 2345 | 1.235e+2345
2054 45678 | 1.235e+45678
2055 131070 | 1.235e+131070
2056 131071 | 1.235e+131071
2057 (22 rows)
2059 WITH v(val) AS
2060 (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan'))
2061 SELECT val,
2062 to_char(val, 'MI9999999999.99') as numeric,
2063 to_char(val::float8, 'MI9999999999.99') as float8,
2064 to_char(val::float4, 'MI9999999999.99') as float4
2065 FROM v;
2066 val | numeric | float8 | float4
2067 ------------+----------------+----------------+----------------
2068 0 | .00 | .00 | .00
2069 -4.2 | - 4.20 | - 4.20 | - 4.20
2070 4200000000 | 4200000000.00 | 4200000000.00 | 4200000000
2071 0.000012 | .00 | .00 | .00
2072 Infinity | Infinity | Infinity | Infinity
2073 -Infinity | - Infinity | - Infinity | - Infinity
2074 NaN | NaN | NaN | NaN
2075 (7 rows)
2077 WITH v(val) AS
2078 (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan'))
2079 SELECT val,
2080 to_char(val, 'MI99.99') as numeric,
2081 to_char(val::float8, 'MI99.99') as float8,
2082 to_char(val::float4, 'MI99.99') as float4
2083 FROM v;
2084 val | numeric | float8 | float4
2085 ------------+---------+--------+--------
2086 0 | .00 | .00 | .00
2087 -4.2 | - 4.20 | - 4.20 | - 4.20
2088 4200000000 | ##.## | ##.## | ##.
2089 0.000012 | .00 | .00 | .00
2090 Infinity | ##.## | ##.## | ##.
2091 -Infinity | -##.## | -##.## | -##.
2092 NaN | ##.## | ##.## | ##.##
2093 (7 rows)
2095 SELECT to_char('100'::numeric, 'FM999.9');
2096 to_char
2097 ---------
2098 100.
2099 (1 row)
2101 SELECT to_char('100'::numeric, 'FM999.');
2102 to_char
2103 ---------
2104 100
2105 (1 row)
2107 SELECT to_char('100'::numeric, 'FM999');
2108 to_char
2109 ---------
2110 100
2111 (1 row)
2113 SELECT to_char('12345678901'::float8, 'FM9999999999D9999900000000000000000');
2114 to_char
2115 -----------------
2116 ##########.####
2117 (1 row)
2119 SELECT to_char('100'::numeric, 'rn');
2120 to_char
2121 -----------------
2122 c
2123 (1 row)
2125 SELECT to_char('1234'::numeric, 'rn');
2126 to_char
2127 -----------------
2128 mccxxxiv
2129 (1 row)
2131 SELECT to_char('1235'::float4, 'rn');
2132 to_char
2133 -----------------
2134 mccxxxv
2135 (1 row)
2137 SELECT to_char('1236'::float8, 'rn');
2138 to_char
2139 -----------------
2140 mccxxxvi
2141 (1 row)
2143 SELECT to_char('1237'::float8, 'fmrn');
2144 to_char
2145 -----------
2146 mccxxxvii
2147 (1 row)
2149 SELECT to_char('100e9'::numeric, 'RN');
2150 to_char
2151 -----------------
2152 ###############
2153 (1 row)
2155 SELECT to_char('100e9'::float4, 'RN');
2156 to_char
2157 -----------------
2158 ###############
2159 (1 row)
2161 SELECT to_char('100e9'::float8, 'RN');
2162 to_char
2163 -----------------
2164 ###############
2165 (1 row)
2167 SELECT to_char(1234.56::numeric, '99999V99');
2168 to_char
2169 ----------
2170 123456
2171 (1 row)
2173 SELECT to_char(1234.56::float4, '99999V99');
2174 to_char
2175 ----------
2176 123456
2177 (1 row)
2179 SELECT to_char(1234.56::float8, '99999V99');
2180 to_char
2181 ----------
2182 123456
2183 (1 row)
2185 -- Check parsing of literal text in a format string
2186 SELECT to_char('100'::numeric, 'foo999');
2187 to_char
2188 ---------
2189 foo 100
2190 (1 row)
2192 SELECT to_char('100'::numeric, 'f\oo999');
2193 to_char
2194 ----------
2195 f\oo 100
2196 (1 row)
2198 SELECT to_char('100'::numeric, 'f\\oo999');
2199 to_char
2200 -----------
2201 f\\oo 100
2202 (1 row)
2204 SELECT to_char('100'::numeric, 'f\"oo999');
2205 to_char
2206 ----------
2207 f"oo 100
2208 (1 row)
2210 SELECT to_char('100'::numeric, 'f\\"oo999');
2211 to_char
2212 -----------
2213 f\"oo 100
2214 (1 row)
2216 SELECT to_char('100'::numeric, 'f"ool"999');
2217 to_char
2218 ----------
2219 fool 100
2220 (1 row)
2222 SELECT to_char('100'::numeric, 'f"\ool"999');
2223 to_char
2224 ----------
2225 fool 100
2226 (1 row)
2228 SELECT to_char('100'::numeric, 'f"\\ool"999');
2229 to_char
2230 -----------
2231 f\ool 100
2232 (1 row)
2234 SELECT to_char('100'::numeric, 'f"ool\"999');
2235 to_char
2236 ----------
2237 fool"999
2238 (1 row)
2240 SELECT to_char('100'::numeric, 'f"ool\\"999');
2241 to_char
2242 -----------
2243 fool\ 100
2244 (1 row)
2246 -- TO_NUMBER()
2247 --
2248 SET lc_numeric = 'C';
2249 SELECT to_number('-34,338,492', '99G999G999');
2250 to_number
2251 -----------
2252 -34338492
2253 (1 row)
2255 SELECT to_number('-34,338,492.654,878', '99G999G999D999G999');
2256 to_number
2257 ------------------
2258 -34338492.654878
2259 (1 row)
2261 SELECT to_number('<564646.654564>', '999999.999999PR');
2262 to_number
2263 ----------------
2264 -564646.654564
2265 (1 row)
2267 SELECT to_number('0.00001-', '9.999999S');
2268 to_number
2269 -----------
2270 -0.00001
2271 (1 row)
2273 SELECT to_number('5.01-', 'FM9.999999S');
2274 to_number
2275 -----------
2276 -5.01
2277 (1 row)
2279 SELECT to_number('5.01-', 'FM9.999999MI');
2280 to_number
2281 -----------
2282 -5.01
2283 (1 row)
2285 SELECT to_number('5 4 4 4 4 8 . 7 8', '9 9 9 9 9 9 . 9 9');
2286 to_number
2287 -----------
2288 544448.78
2289 (1 row)
2291 SELECT to_number('.01', 'FM9.99');
2292 to_number
2293 -----------
2294 0.01
2295 (1 row)
2297 SELECT to_number('.0', '99999999.99999999');
2298 to_number
2299 -----------
2300 0.0
2301 (1 row)
2303 SELECT to_number('0', '99.99');
2304 to_number
2305 -----------
2306 0
2307 (1 row)
2309 SELECT to_number('.-01', 'S99.99');
2310 to_number
2311 -----------
2312 -0.01
2313 (1 row)
2315 SELECT to_number('.01-', '99.99S');
2316 to_number
2317 -----------
2318 -0.01
2319 (1 row)
2321 SELECT to_number(' . 0 1-', ' 9 9 . 9 9 S');
2322 to_number
2323 -----------
2324 -0.01
2325 (1 row)
2327 SELECT to_number('34,50','999,99');
2328 to_number
2329 -----------
2330 3450
2331 (1 row)
2333 SELECT to_number('123,000','999G');
2334 to_number
2335 -----------
2336 123
2337 (1 row)
2339 SELECT to_number('123456','999G999');
2340 to_number
2341 -----------
2342 123456
2343 (1 row)
2345 SELECT to_number('$1234.56','L9,999.99');
2346 to_number
2347 -----------
2348 1234.56
2349 (1 row)
2351 SELECT to_number('$1234.56','L99,999.99');
2352 to_number
2353 -----------
2354 1234.56
2355 (1 row)
2357 SELECT to_number('$1,234.56','L99,999.99');
2358 to_number
2359 -----------
2360 1234.56
2361 (1 row)
2363 SELECT to_number('1234.56','L99,999.99');
2364 to_number
2365 -----------
2366 1234.56
2367 (1 row)
2369 SELECT to_number('1,234.56','L99,999.99');
2370 to_number
2371 -----------
2372 1234.56
2373 (1 row)
2375 SELECT to_number('42nd', '99th');
2376 to_number
2377 -----------
2378 42
2379 (1 row)
2381 SELECT to_number('123456', '99999V99');
2382 to_number
2383 -------------------------
2384 1234.560000000000000000
2385 (1 row)
2387 RESET lc_numeric;
2388 --
2389 -- Input syntax
2390 --
2391 CREATE TABLE num_input_test (n1 numeric);
2392 -- good inputs
2393 INSERT INTO num_input_test(n1) VALUES (' 123');
2394 INSERT INTO num_input_test(n1) VALUES (' 3245874 ');
2395 INSERT INTO num_input_test(n1) VALUES (' -93853');
2396 INSERT INTO num_input_test(n1) VALUES ('555.50');
2397 INSERT INTO num_input_test(n1) VALUES ('-555.50');
2398 INSERT INTO num_input_test(n1) VALUES ('NaN ');
2399 INSERT INTO num_input_test(n1) VALUES (' nan');
2400 INSERT INTO num_input_test(n1) VALUES (' inf ');
2401 INSERT INTO num_input_test(n1) VALUES (' +inf ');
2402 INSERT INTO num_input_test(n1) VALUES (' -inf ');
2403 INSERT INTO num_input_test(n1) VALUES (' Infinity ');
2404 INSERT INTO num_input_test(n1) VALUES (' +inFinity ');
2405 INSERT INTO num_input_test(n1) VALUES (' -INFINITY ');
2406 INSERT INTO num_input_test(n1) VALUES ('12_000_000_000');
2407 INSERT INTO num_input_test(n1) VALUES ('12_000.123_456');
2408 INSERT INTO num_input_test(n1) VALUES ('23_000_000_000e-1_0');
2409 INSERT INTO num_input_test(n1) VALUES ('.000_000_000_123e1_0');
2410 INSERT INTO num_input_test(n1) VALUES ('.000_000_000_123e+1_1');
2411 INSERT INTO num_input_test(n1) VALUES ('0b10001110111100111100001001010');
2412 INSERT INTO num_input_test(n1) VALUES (' -0B_1010_1011_0101_0100_1010_1001_1000_1100_1110_1011_0001_1111_0000_1010_1101_0010 ');
2413 INSERT INTO num_input_test(n1) VALUES (' +0o112402761777 ');
2414 INSERT INTO num_input_test(n1) VALUES ('-0O0012_5524_5230_6334_3167_0261');
2415 INSERT INTO num_input_test(n1) VALUES ('-0x0000000000000000000000000deadbeef');
2416 INSERT INTO num_input_test(n1) VALUES (' 0X_30b1_F33a_6DF0_bD4E_64DF_9BdA_7D15 ');
2417 -- bad inputs
2418 INSERT INTO num_input_test(n1) VALUES (' ');
2419 ERROR: invalid input syntax for type numeric: " "
2420 LINE 1: INSERT INTO num_input_test(n1) VALUES (' ');
2421 ^
2422 INSERT INTO num_input_test(n1) VALUES (' 1234 %');
2423 ERROR: invalid input syntax for type numeric: " 1234 %"
2424 LINE 1: INSERT INTO num_input_test(n1) VALUES (' 1234 %');
2425 ^
2426 INSERT INTO num_input_test(n1) VALUES ('xyz');
2427 ERROR: invalid input syntax for type numeric: "xyz"
2428 LINE 1: INSERT INTO num_input_test(n1) VALUES ('xyz');
2429 ^
2430 INSERT INTO num_input_test(n1) VALUES ('- 1234');
2431 ERROR: invalid input syntax for type numeric: "- 1234"
2432 LINE 1: INSERT INTO num_input_test(n1) VALUES ('- 1234');
2433 ^
2434 INSERT INTO num_input_test(n1) VALUES ('5 . 0');
2435 ERROR: invalid input syntax for type numeric: "5 . 0"
2436 LINE 1: INSERT INTO num_input_test(n1) VALUES ('5 . 0');
2437 ^
2438 INSERT INTO num_input_test(n1) VALUES ('5. 0 ');
2439 ERROR: invalid input syntax for type numeric: "5. 0 "
2440 LINE 1: INSERT INTO num_input_test(n1) VALUES ('5. 0 ');
2441 ^
2442 INSERT INTO num_input_test(n1) VALUES ('');
2443 ERROR: invalid input syntax for type numeric: ""
2444 LINE 1: INSERT INTO num_input_test(n1) VALUES ('');
2445 ^
2446 INSERT INTO num_input_test(n1) VALUES (' N aN ');
2447 ERROR: invalid input syntax for type numeric: " N aN "
2448 LINE 1: INSERT INTO num_input_test(n1) VALUES (' N aN ');
2449 ^
2450 INSERT INTO num_input_test(n1) VALUES ('+NaN');
2451 ERROR: invalid input syntax for type numeric: "+NaN"
2452 LINE 1: INSERT INTO num_input_test(n1) VALUES ('+NaN');
2453 ^
2454 INSERT INTO num_input_test(n1) VALUES ('-NaN');
2455 ERROR: invalid input syntax for type numeric: "-NaN"
2456 LINE 1: INSERT INTO num_input_test(n1) VALUES ('-NaN');
2457 ^
2458 INSERT INTO num_input_test(n1) VALUES ('+ infinity');
2459 ERROR: invalid input syntax for type numeric: "+ infinity"
2460 LINE 1: INSERT INTO num_input_test(n1) VALUES ('+ infinity');
2461 ^
2462 INSERT INTO num_input_test(n1) VALUES ('_123');
2463 ERROR: invalid input syntax for type numeric: "_123"
2464 LINE 1: INSERT INTO num_input_test(n1) VALUES ('_123');
2465 ^
2466 INSERT INTO num_input_test(n1) VALUES ('123_');
2467 ERROR: invalid input syntax for type numeric: "123_"
2468 LINE 1: INSERT INTO num_input_test(n1) VALUES ('123_');
2469 ^
2470 INSERT INTO num_input_test(n1) VALUES ('12__34');
2471 ERROR: invalid input syntax for type numeric: "12__34"
2472 LINE 1: INSERT INTO num_input_test(n1) VALUES ('12__34');
2473 ^
2474 INSERT INTO num_input_test(n1) VALUES ('123_.456');
2475 ERROR: invalid input syntax for type numeric: "123_.456"
2476 LINE 1: INSERT INTO num_input_test(n1) VALUES ('123_.456');
2477 ^
2478 INSERT INTO num_input_test(n1) VALUES ('123._456');
2479 ERROR: invalid input syntax for type numeric: "123._456"
2480 LINE 1: INSERT INTO num_input_test(n1) VALUES ('123._456');
2481 ^
2482 INSERT INTO num_input_test(n1) VALUES ('1.2e_34');
2483 ERROR: invalid input syntax for type numeric: "1.2e_34"
2484 LINE 1: INSERT INTO num_input_test(n1) VALUES ('1.2e_34');
2485 ^
2486 INSERT INTO num_input_test(n1) VALUES ('1.2e34_');
2487 ERROR: invalid input syntax for type numeric: "1.2e34_"
2488 LINE 1: INSERT INTO num_input_test(n1) VALUES ('1.2e34_');
2489 ^
2490 INSERT INTO num_input_test(n1) VALUES ('1.2e3__4');
2491 ERROR: invalid input syntax for type numeric: "1.2e3__4"
2492 LINE 1: INSERT INTO num_input_test(n1) VALUES ('1.2e3__4');
2493 ^
2494 INSERT INTO num_input_test(n1) VALUES ('0b1112');
2495 ERROR: invalid input syntax for type numeric: "0b1112"
2496 LINE 1: INSERT INTO num_input_test(n1) VALUES ('0b1112');
2497 ^
2498 INSERT INTO num_input_test(n1) VALUES ('0c1112');
2499 ERROR: invalid input syntax for type numeric: "0c1112"
2500 LINE 1: INSERT INTO num_input_test(n1) VALUES ('0c1112');
2501 ^
2502 INSERT INTO num_input_test(n1) VALUES ('0o12345678');
2503 ERROR: invalid input syntax for type numeric: "0o12345678"
2504 LINE 1: INSERT INTO num_input_test(n1) VALUES ('0o12345678');
2505 ^
2506 INSERT INTO num_input_test(n1) VALUES ('0x1eg');
2507 ERROR: invalid input syntax for type numeric: "0x1eg"
2508 LINE 1: INSERT INTO num_input_test(n1) VALUES ('0x1eg');
2509 ^
2510 INSERT INTO num_input_test(n1) VALUES ('0x12.34');
2511 ERROR: invalid input syntax for type numeric: "0x12.34"
2512 LINE 1: INSERT INTO num_input_test(n1) VALUES ('0x12.34');
2513 ^
2514 INSERT INTO num_input_test(n1) VALUES ('0x__1234');
2515 ERROR: invalid input syntax for type numeric: "0x__1234"
2516 LINE 1: INSERT INTO num_input_test(n1) VALUES ('0x__1234');
2517 ^
2518 INSERT INTO num_input_test(n1) VALUES ('0x1234_');
2519 ERROR: invalid input syntax for type numeric: "0x1234_"
2520 LINE 1: INSERT INTO num_input_test(n1) VALUES ('0x1234_');
2521 ^
2522 INSERT INTO num_input_test(n1) VALUES ('0x12__34');
2523 ERROR: invalid input syntax for type numeric: "0x12__34"
2524 LINE 1: INSERT INTO num_input_test(n1) VALUES ('0x12__34');
2525 ^
2526 SELECT * FROM num_input_test;
2527 n1
2528 -----------------------------------
2529 123
2530 3245874
2531 -93853
2532 555.50
2533 -555.50
2534 NaN
2535 NaN
2536 Infinity
2537 Infinity
2538 -Infinity
2539 Infinity
2540 Infinity
2541 -Infinity
2542 12000000000
2543 12000.123456
2544 2.3000000000
2545 1.23
2546 12.3
2547 299792458
2548 -12345678901234567890
2549 9999999999
2550 -12345678900987654321
2551 -3735928559
2552 987654321234567898765432123456789
2553 (24 rows)
2555 -- Also try it with non-error-throwing API
2556 SELECT pg_input_is_valid('34.5', 'numeric');
2557 pg_input_is_valid
2558 -------------------
2559 t
2560 (1 row)
2562 SELECT pg_input_is_valid('34xyz', 'numeric');
2563 pg_input_is_valid
2564 -------------------
2565 f
2566 (1 row)
2568 SELECT pg_input_is_valid('1e400000', 'numeric');
2569 pg_input_is_valid
2570 -------------------
2571 f
2572 (1 row)
2574 SELECT * FROM pg_input_error_info('1e400000', 'numeric');
2575 message | detail | hint | sql_error_code
2576 --------------------------------+--------+------+----------------
2577 value overflows numeric format | | | 22003
2578 (1 row)
2580 SELECT pg_input_is_valid('1234.567', 'numeric(8,4)');
2581 pg_input_is_valid
2582 -------------------
2583 t
2584 (1 row)
2586 SELECT pg_input_is_valid('1234.567', 'numeric(7,4)');
2587 pg_input_is_valid
2588 -------------------
2589 f
2590 (1 row)
2592 SELECT * FROM pg_input_error_info('1234.567', 'numeric(7,4)');
2593 message | detail | hint | sql_error_code
2594 ------------------------+-----------------------------------------------------------------------------------+------+----------------
2595 numeric field overflow | A field with precision 7, scale 4 must round to an absolute value less than 10^3. | | 22003
2596 (1 row)
2598 SELECT * FROM pg_input_error_info('0x1234.567', 'numeric');
2599 message | detail | hint | sql_error_code
2600 -----------------------------------------------------+--------+------+----------------
2601 invalid input syntax for type numeric: "0x1234.567" | | | 22P02
2602 (1 row)
2604 --
2605 -- Test precision and scale typemods
2606 --
2607 CREATE TABLE num_typemod_test (
2608 millions numeric(3, -6),
2609 thousands numeric(3, -3),
2610 units numeric(3, 0),
2611 thousandths numeric(3, 3),
2612 millionths numeric(3, 6)
2613 );
2614 \d num_typemod_test
2615 Table "public.num_typemod_test"
2616 Column | Type | Collation | Nullable | Default
2617 -------------+---------------+-----------+----------+---------
2618 millions | numeric(3,-6) | | |
2619 thousands | numeric(3,-3) | | |
2620 units | numeric(3,0) | | |
2621 thousandths | numeric(3,3) | | |
2622 millionths | numeric(3,6) | | |
2624 -- rounding of valid inputs
2625 INSERT INTO num_typemod_test VALUES (123456, 123, 0.123, 0.000123, 0.000000123);
2626 INSERT INTO num_typemod_test VALUES (654321, 654, 0.654, 0.000654, 0.000000654);
2627 INSERT INTO num_typemod_test VALUES (2345678, 2345, 2.345, 0.002345, 0.000002345);
2628 INSERT INTO num_typemod_test VALUES (7654321, 7654, 7.654, 0.007654, 0.000007654);
2629 INSERT INTO num_typemod_test VALUES (12345678, 12345, 12.345, 0.012345, 0.000012345);
2630 INSERT INTO num_typemod_test VALUES (87654321, 87654, 87.654, 0.087654, 0.000087654);
2631 INSERT INTO num_typemod_test VALUES (123456789, 123456, 123.456, 0.123456, 0.000123456);
2632 INSERT INTO num_typemod_test VALUES (987654321, 987654, 987.654, 0.987654, 0.000987654);
2633 INSERT INTO num_typemod_test VALUES ('NaN', 'NaN', 'NaN', 'NaN', 'NaN');
2634 SELECT scale(millions), * FROM num_typemod_test ORDER BY millions;
2635 scale | millions | thousands | units | thousandths | millionths
2636 -------+-----------+-----------+-------+-------------+------------
2637 0 | 0 | 0 | 0 | 0.000 | 0.000000
2638 0 | 1000000 | 1000 | 1 | 0.001 | 0.000001
2639 0 | 2000000 | 2000 | 2 | 0.002 | 0.000002
2640 0 | 8000000 | 8000 | 8 | 0.008 | 0.000008
2641 0 | 12000000 | 12000 | 12 | 0.012 | 0.000012
2642 0 | 88000000 | 88000 | 88 | 0.088 | 0.000088
2643 0 | 123000000 | 123000 | 123 | 0.123 | 0.000123
2644 0 | 988000000 | 988000 | 988 | 0.988 | 0.000988
2645 | NaN | NaN | NaN | NaN | NaN
2646 (9 rows)
2648 -- invalid inputs
2649 INSERT INTO num_typemod_test (millions) VALUES ('inf');
2650 ERROR: numeric field overflow
2651 DETAIL: A field with precision 3, scale -6 cannot hold an infinite value.
2652 INSERT INTO num_typemod_test (millions) VALUES (999500000);
2653 ERROR: numeric field overflow
2654 DETAIL: A field with precision 3, scale -6 must round to an absolute value less than 10^9.
2655 INSERT INTO num_typemod_test (thousands) VALUES (999500);
2656 ERROR: numeric field overflow
2657 DETAIL: A field with precision 3, scale -3 must round to an absolute value less than 10^6.
2658 INSERT INTO num_typemod_test (units) VALUES (999.5);
2659 ERROR: numeric field overflow
2660 DETAIL: A field with precision 3, scale 0 must round to an absolute value less than 10^3.
2661 INSERT INTO num_typemod_test (thousandths) VALUES (0.9995);
2662 ERROR: numeric field overflow
2663 DETAIL: A field with precision 3, scale 3 must round to an absolute value less than 1.
2664 INSERT INTO num_typemod_test (millionths) VALUES (0.0009995);
2665 ERROR: numeric field overflow
2666 DETAIL: A field with precision 3, scale 6 must round to an absolute value less than 10^-3.
2667 --
2668 -- Test some corner cases for multiplication
2669 --
2670 select 4790999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
2671 ?column?
2672 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2673 47909999999999999999999999999999999999999999999999999999999999999999999999999999999999985209000000000000000000000000000000000000000000000000000000000000000000000000000000000001
2674 (1 row)
2676 select 4789999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
2677 ?column?
2678 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2679 47899999999999999999999999999999999999999999999999999999999999999999999999999999999999985210000000000000000000000000000000000000000000000000000000000000000000000000000000000001
2680 (1 row)
2682 select 4770999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
2683 ?column?
2684 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2685 47709999999999999999999999999999999999999999999999999999999999999999999999999999999999985229000000000000000000000000000000000000000000000000000000000000000000000000000000000001
2686 (1 row)
2688 select 4769999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
2689 ?column?
2690 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2691 47699999999999999999999999999999999999999999999999999999999999999999999999999999999999985230000000000000000000000000000000000000000000000000000000000000000000000000000000000001
2692 (1 row)
2694 select trim_scale((0.1 - 2e-16383) * (0.1 - 3e-16383));
2695 trim_scale
2696 ------------
2697 0.01
2698 (1 row)
2700 --
2701 -- Test some corner cases for division
2702 --
2703 select 999999999999999999999::numeric/1000000000000000000000;
2704 ?column?
2705 ------------------------
2706 1.00000000000000000000
2707 (1 row)
2709 select div(999999999999999999999::numeric,1000000000000000000000);
2710 div
2711 -----
2712 0
2713 (1 row)
2715 select mod(999999999999999999999::numeric,1000000000000000000000);
2716 mod
2717 -----------------------
2718 999999999999999999999
2719 (1 row)
2721 select div(-9999999999999999999999::numeric,1000000000000000000000);
2722 div
2723 -----
2724 -9
2725 (1 row)
2727 select mod(-9999999999999999999999::numeric,1000000000000000000000);
2728 mod
2729 ------------------------
2730 -999999999999999999999
2731 (1 row)
2733 select div(-9999999999999999999999::numeric,1000000000000000000000)*1000000000000000000000 + mod(-9999999999999999999999::numeric,1000000000000000000000);
2734 ?column?
2735 -------------------------
2736 -9999999999999999999999
2737 (1 row)
2739 select mod (70.0,70) ;
2740 mod
2741 -----
2742 0.0
2743 (1 row)
2745 select div (70.0,70) ;
2746 div
2747 -----
2748 1
2749 (1 row)
2751 select 70.0 / 70 ;
2752 ?column?
2753 ------------------------
2754 1.00000000000000000000
2755 (1 row)
2757 select 12345678901234567890 % 123;
2758 ?column?
2759 ----------
2760 78
2761 (1 row)
2763 select 12345678901234567890 / 123;
2764 ?column?
2765 --------------------
2766 100371373180768845
2767 (1 row)
2769 select div(12345678901234567890, 123);
2770 div
2771 --------------------
2772 100371373180768844
2773 (1 row)
2775 select div(12345678901234567890, 123) * 123 + 12345678901234567890 % 123;
2776 ?column?
2777 ----------------------
2778 12345678901234567890
2779 (1 row)
2781 select 8e9000 - div(8e18000 - 1, 9e9000 - 1) * 9;
2782 ?column?
2783 ----------
2784 8
2785 (1 row)
2787 select 7328412092 - div(53705623790171816464 - 1, 7328412092);
2788 ?column?
2789 ----------
2790 1
2791 (1 row)
2793 select div(539913372912345678, 539913372912345678);
2794 div
2795 -----
2796 1
2797 (1 row)
2799 --
2800 -- Test some corner cases for square root
2801 --
2802 select sqrt(1.000000000000003::numeric);
2803 sqrt
2804 -------------------
2805 1.000000000000001
2806 (1 row)
2808 select sqrt(1.000000000000004::numeric);
2809 sqrt
2810 -------------------
2811 1.000000000000002
2812 (1 row)
2814 select sqrt(96627521408608.56340355805::numeric);
2815 sqrt
2816 ---------------------
2817 9829929.87811248648
2818 (1 row)
2820 select sqrt(96627521408608.56340355806::numeric);
2821 sqrt
2822 ---------------------
2823 9829929.87811248649
2824 (1 row)
2826 select sqrt(515549506212297735.073688290367::numeric);
2827 sqrt
2828 ------------------------
2829 718017761.766585921184
2830 (1 row)
2832 select sqrt(515549506212297735.073688290368::numeric);
2833 sqrt
2834 ------------------------
2835 718017761.766585921185
2836 (1 row)
2838 select sqrt(8015491789940783531003294973900306::numeric);
2839 sqrt
2840 -------------------
2841 89529278953540017
2842 (1 row)
2844 select sqrt(8015491789940783531003294973900307::numeric);
2845 sqrt
2846 -------------------
2847 89529278953540018
2848 (1 row)
2850 --
2851 -- Test code path for raising to integer powers
2852 --
2853 select 10.0 ^ -2147483648 as rounds_to_zero;
2854 rounds_to_zero
2855 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2856 0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
2857 (1 row)
2859 select 10.0 ^ -2147483647 as rounds_to_zero;
2860 rounds_to_zero
2861 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2862 0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
2863 (1 row)
2865 select 10.0 ^ 2147483647 as overflows;
2866 ERROR: value overflows numeric format
2867 select 117743296169.0 ^ 1000000000 as overflows;
2868 ERROR: value overflows numeric format
2869 -- cases that used to return inaccurate results
2870 select 3.789 ^ 21.0000000000000000;
2871 ?column?
2872 --------------------------------
2873 1409343026052.8716016316022141
2874 (1 row)
2876 select 3.789 ^ 35.0000000000000000;
2877 ?column?
2878 ----------------------------------------
2879 177158169650516670809.3820586142670135
2880 (1 row)
2882 select 1.2 ^ 345;
2883 ?column?
2884 --------------------------------
2885 2077446682327378559843444695.6
2886 (1 row)
2888 select 0.12 ^ (-20);
2889 ?column?
2890 ------------------------
2891 2608405330458882702.55
2892 (1 row)
2894 select 1.000000000123 ^ (-2147483648);
2895 ?column?
2896 --------------------
2897 0.7678656556403084
2898 (1 row)
2900 select coalesce(nullif(0.9999999999 ^ 23300000000000, 0), 0) as rounds_to_zero;
2901 rounds_to_zero
2902 ----------------
2903 0
2904 (1 row)
2906 select round(((1 - 1.500012345678e-1000) ^ 1.45e1003) * 1e1000);
2907 round
2908 ----------------------------------------------------------
2909 25218976308958387188077465658068501556514992509509282366
2910 (1 row)
2912 -- cases that used to error out
2913 select 0.12 ^ (-25);
2914 ?column?
2915 -----------------------------
2916 104825960103961013959336.50
2917 (1 row)
2919 select 0.5678 ^ (-85);
2920 ?column?
2921 ----------------------------
2922 782333637740774446257.7719
2923 (1 row)
2925 select coalesce(nullif(0.9999999999 ^ 70000000000000, 0), 0) as underflows;
2926 underflows
2927 ------------
2928 0
2929 (1 row)
2931 -- negative base to integer powers
2932 select (-1.0) ^ 2147483646;
2933 ?column?
2934 --------------------
2935 1.0000000000000000
2936 (1 row)
2938 select (-1.0) ^ 2147483647;
2939 ?column?
2940 ---------------------
2941 -1.0000000000000000
2942 (1 row)
2944 select (-1.0) ^ 2147483648;
2945 ?column?
2946 --------------------
2947 1.0000000000000000
2948 (1 row)
2950 select (-1.0) ^ 1000000000000000;
2951 ?column?
2952 --------------------
2953 1.0000000000000000
2954 (1 row)
2956 select (-1.0) ^ 1000000000000001;
2957 ?column?
2958 ---------------------
2959 -1.0000000000000000
2960 (1 row)
2962 -- integer powers of 10
2963 select n, 10.0 ^ n as "10^n", (10.0 ^ n) * (10.0 ^ (-n)) = 1 as ok
2964 from generate_series(-20, 20) n;
2965 n | 10^n | ok
2966 -----+----------------------------------------+----
2967 -20 | 0.000000000000000000010000000000000000 | t
2968 -19 | 0.00000000000000000010000000000000000 | t
2969 -18 | 0.0000000000000000010000000000000000 | t
2970 -17 | 0.000000000000000010000000000000000 | t
2971 -16 | 0.00000000000000010000000000000000 | t
2972 -15 | 0.0000000000000010000000000000000 | t
2973 -14 | 0.000000000000010000000000000000 | t
2974 -13 | 0.00000000000010000000000000000 | t
2975 -12 | 0.0000000000010000000000000000 | t
2976 -11 | 0.000000000010000000000000000 | t
2977 -10 | 0.00000000010000000000000000 | t
2978 -9 | 0.0000000010000000000000000 | t
2979 -8 | 0.000000010000000000000000 | t
2980 -7 | 0.00000010000000000000000 | t
2981 -6 | 0.0000010000000000000000 | t
2982 -5 | 0.000010000000000000000 | t
2983 -4 | 0.00010000000000000000 | t
2984 -3 | 0.0010000000000000000 | t
2985 -2 | 0.010000000000000000 | t
2986 -1 | 0.10000000000000000 | t
2987 0 | 1.0000000000000000 | t
2988 1 | 10.000000000000000 | t
2989 2 | 100.00000000000000 | t
2990 3 | 1000.0000000000000 | t
2991 4 | 10000.000000000000 | t
2992 5 | 100000.00000000000 | t
2993 6 | 1000000.0000000000 | t
2994 7 | 10000000.000000000 | t
2995 8 | 100000000.00000000 | t
2996 9 | 1000000000.0000000 | t
2997 10 | 10000000000.000000 | t
2998 11 | 100000000000.00000 | t
2999 12 | 1000000000000.0000 | t
3000 13 | 10000000000000.000 | t
3001 14 | 100000000000000.00 | t
3002 15 | 1000000000000000.0 | t
3003 16 | 10000000000000000.0 | t
3004 17 | 100000000000000000.0 | t
3005 18 | 1000000000000000000.0 | t
3006 19 | 10000000000000000000.0 | t
3007 20 | 100000000000000000000.0 | t
3008 (41 rows)
3010 --
3011 -- Tests for raising to non-integer powers
3012 --
3013 -- special cases
3014 select 0.0 ^ 0.0;
3015 ?column?
3016 --------------------
3017 1.0000000000000000
3018 (1 row)
3020 select (-12.34) ^ 0.0;
3021 ?column?
3022 --------------------
3023 1.0000000000000000
3024 (1 row)
3026 select 12.34 ^ 0.0;
3027 ?column?
3028 --------------------
3029 1.0000000000000000
3030 (1 row)
3032 select 0.0 ^ 12.34;
3033 ?column?
3034 --------------------
3035 0.0000000000000000
3036 (1 row)
3038 -- NaNs
3039 select 'NaN'::numeric ^ 'NaN'::numeric;
3040 ?column?
3041 ----------
3042 NaN
3043 (1 row)
3045 select 'NaN'::numeric ^ 0;
3046 ?column?
3047 ----------
3048 1
3049 (1 row)
3051 select 'NaN'::numeric ^ 1;
3052 ?column?
3053 ----------
3054 NaN
3055 (1 row)
3057 select 0 ^ 'NaN'::numeric;
3058 ?column?
3059 ----------
3060 NaN
3061 (1 row)
3063 select 1 ^ 'NaN'::numeric;
3064 ?column?
3065 ----------
3066 1
3067 (1 row)
3069 -- invalid inputs
3070 select 0.0 ^ (-12.34);
3071 ERROR: zero raised to a negative power is undefined
3072 select (-12.34) ^ 1.2;
3073 ERROR: a negative number raised to a non-integer power yields a complex result
3074 -- cases that used to generate inaccurate results
3075 select 32.1 ^ 9.8;
3076 ?column?
3077 --------------------
3078 580429286790711.10
3079 (1 row)
3081 select 32.1 ^ (-9.8);
3082 ?column?
3083 ----------------------------------
3084 0.000000000000001722862754788209
3085 (1 row)
3087 select 12.3 ^ 45.6;
3088 ?column?
3089 ------------------------------------------------------
3090 50081010321492803393171165777624533697036806969694.9
3091 (1 row)
3093 select 12.3 ^ (-45.6);
3094 ?column?
3095 ---------------------------------------------------------------------
3096 0.00000000000000000000000000000000000000000000000001996764828785491
3097 (1 row)
3099 -- big test
3100 select 1.234 ^ 5678;
3101 ?column?
3102 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3103 307239295662090741644584872593956173493568238595074141254349565406661439636598896798876823220904084953233015553994854875890890858118656468658643918169805277399402542281777901029346337707622181574346585989613344285010764501017625366742865066948856161360224801370482171458030533346309750557140549621313515752078638620714732831815297168231790779296290266207315344008883935010274044001522606235576584215999260117523114297033944018699691024106823438431754073086813382242140602291215149759520833200152654884259619588924545324.597
3104 (1 row)
3106 --
3107 -- Tests for EXP()
3108 --
3109 -- special cases
3110 select exp(0.0);
3111 exp
3112 --------------------
3113 1.0000000000000000
3114 (1 row)
3116 select exp(1.0);
3117 exp
3118 --------------------
3119 2.7182818284590452
3120 (1 row)
3122 select exp(1.0::numeric(71,70));
3123 exp
3124 --------------------------------------------------------------------------
3125 2.7182818284590452353602874713526624977572470936999595749669676277240766
3126 (1 row)
3128 select exp('nan'::numeric);
3129 exp
3130 -----
3131 NaN
3132 (1 row)
3134 select exp('inf'::numeric);
3135 exp
3136 ----------
3137 Infinity
3138 (1 row)
3140 select exp('-inf'::numeric);
3141 exp
3142 -----
3143 0
3144 (1 row)
3146 select coalesce(nullif(exp(-5000::numeric), 0), 0) as rounds_to_zero;
3147 rounds_to_zero
3148 ----------------
3149 0
3150 (1 row)
3152 select coalesce(nullif(exp(-10000::numeric), 0), 0) as underflows;
3153 underflows
3154 ------------
3155 0
3156 (1 row)
3158 -- cases that used to generate inaccurate results
3159 select exp(32.999);
3160 exp
3161 ---------------------
3162 214429043492155.053
3163 (1 row)
3165 select exp(-32.999);
3166 exp
3167 ----------------------------------
3168 0.000000000000004663547361468248
3169 (1 row)
3171 select exp(123.456);
3172 exp
3173 ------------------------------------------------------------
3174 413294435277809344957685441227343146614594393746575438.725
3175 (1 row)
3177 select exp(-123.456);
3178 exp
3179 -------------------------------------------------------------------------
3180 0.000000000000000000000000000000000000000000000000000002419582541264601
3181 (1 row)
3183 -- big test
3184 select exp(1234.5678);
3185 exp
3186 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3187 146549072930959479983482138503979804217622199675223653966270157446954995433819741094410764947112047906012815540251009949604426069672532417736057033099274204598385314594846509975629046864798765888104789074984927709616261452461385220475510438783429612447831614003668421849727379202555580791042606170523016207262965336641214601082882495255771621327088265411334088968112458492660609809762865582162764292604697957813514621259353683899630997077707406305730694385703091201347848855199354307506425820147289848677003277208302716466011827836279231.9667
3188 (1 row)
3190 --
3191 -- Tests for generate_series
3192 --
3193 select * from generate_series(0.0::numeric, 4.0::numeric);
3194 generate_series
3195 -----------------
3196 0.0
3197 1.0
3198 2.0
3199 3.0
3200 4.0
3201 (5 rows)
3203 select * from generate_series(0.1::numeric, 4.0::numeric, 1.3::numeric);
3204 generate_series
3205 -----------------
3206 0.1
3207 1.4
3208 2.7
3209 4.0
3210 (4 rows)
3212 select * from generate_series(4.0::numeric, -1.5::numeric, -2.2::numeric);
3213 generate_series
3214 -----------------
3215 4.0
3216 1.8
3217 -0.4
3218 (3 rows)
3220 -- Trigger errors
3221 select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
3222 ERROR: step size cannot equal zero
3223 select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
3224 ERROR: step size cannot be NaN
3225 select * from generate_series('nan'::numeric, 100::numeric, 10::numeric);
3226 ERROR: start value cannot be NaN
3227 select * from generate_series(0::numeric, 'nan'::numeric, 10::numeric);
3228 ERROR: stop value cannot be NaN
3229 select * from generate_series('inf'::numeric, 'inf'::numeric, 10::numeric);
3230 ERROR: start value cannot be infinity
3231 select * from generate_series(0::numeric, 'inf'::numeric, 10::numeric);
3232 ERROR: stop value cannot be infinity
3233 select * from generate_series(0::numeric, '42'::numeric, '-inf'::numeric);
3234 ERROR: step size cannot be infinity
3235 -- Checks maximum, output is truncated
3236 select (i / (10::numeric ^ 131071))::numeric(1,0)
3237 from generate_series(6 * (10::numeric ^ 131071),
3238 9 * (10::numeric ^ 131071),
3239 10::numeric ^ 131071) as a(i);
3240 numeric
3241 ---------
3242 6
3243 7
3244 8
3245 9
3246 (4 rows)
3248 -- Check usage with variables
3249 select * from generate_series(1::numeric, 3::numeric) i, generate_series(i,3) j;
3250 i | j
3251 ---+---
3252 1 | 1
3253 1 | 2
3254 1 | 3
3255 2 | 2
3256 2 | 3
3257 3 | 3
3258 (6 rows)
3260 select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,i) j;
3261 i | j
3262 ---+---
3263 1 | 1
3264 2 | 1
3265 2 | 2
3266 3 | 1
3267 3 | 2
3268 3 | 3
3269 (6 rows)
3271 select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,5,i) j;
3272 i | j
3273 ---+---
3274 1 | 1
3275 1 | 2
3276 1 | 3
3277 1 | 4
3278 1 | 5
3279 2 | 1
3280 2 | 3
3281 2 | 5
3282 3 | 1
3283 3 | 4
3284 (10 rows)
3286 --
3287 -- Tests for LN()
3288 --
3289 -- Invalid inputs
3290 select ln(-12.34);
3291 ERROR: cannot take logarithm of a negative number
3292 select ln(0.0);
3293 ERROR: cannot take logarithm of zero
3294 -- Some random tests
3295 select ln(1.2345678e-28);
3296 ln
3297 -----------------------------------------
3298 -64.26166165451762991204894255882820859
3299 (1 row)
3301 select ln(0.0456789);
3302 ln
3303 ---------------------
3304 -3.0861187944847439
3305 (1 row)
3307 select ln(0.349873948359354029493948309745709580730482050975);
3308 ln
3309 -----------------------------------------------------
3310 -1.050182336912082775693991697979750253056317885460
3311 (1 row)
3313 select ln(0.99949452);
3314 ln
3315 -------------------------
3316 -0.00050560779808326467
3317 (1 row)
3319 select ln(1.00049687395);
3320 ln
3321 ------------------------
3322 0.00049675054901370394
3323 (1 row)
3325 select ln(1234.567890123456789);
3326 ln
3327 --------------------
3328 7.1184763012977896
3329 (1 row)
3331 select ln(5.80397490724e5);
3332 ln
3333 --------------------
3334 13.271468476626518
3335 (1 row)
3337 select ln(9.342536355e34);
3338 ln
3339 --------------------
3340 80.522470935524187
3341 (1 row)
3343 --
3344 -- Tests for LOG() (base 10)
3345 --
3346 -- invalid inputs
3347 select log(-12.34);
3348 ERROR: cannot take logarithm of a negative number
3349 CONTEXT: SQL function "log" statement 1
3350 select log(0.0);
3351 ERROR: cannot take logarithm of zero
3352 CONTEXT: SQL function "log" statement 1
3353 -- some random tests
3354 select log(1.234567e-89);
3355 log
3356 -----------------------------------------------------------------------------------------------------
3357 -88.90848533591373725637496492944925187293052336306443143312825869985819779294142441287021741054275
3358 (1 row)
3360 select log(3.4634998359873254962349856073435545);
3361 log
3362 --------------------------------------
3363 0.5395151714070134409152404011959981
3364 (1 row)
3366 select log(9.999999999999999999);
3367 log
3368 ----------------------
3369 1.000000000000000000
3370 (1 row)
3372 select log(10.00000000000000000);
3373 log
3374 ---------------------
3375 1.00000000000000000
3376 (1 row)
3378 select log(10.00000000000000001);
3379 log
3380 ---------------------
3381 1.00000000000000000
3382 (1 row)
3384 select log(590489.45235237);
3385 log
3386 -------------------
3387 5.771212144411727
3388 (1 row)
3390 --
3391 -- Tests for LOG() (arbitrary base)
3392 --
3393 -- invalid inputs
3394 select log(-12.34, 56.78);
3395 ERROR: cannot take logarithm of a negative number
3396 select log(-12.34, -56.78);
3397 ERROR: cannot take logarithm of a negative number
3398 select log(12.34, -56.78);
3399 ERROR: cannot take logarithm of a negative number
3400 select log(0.0, 12.34);
3401 ERROR: cannot take logarithm of zero
3402 select log(12.34, 0.0);
3403 ERROR: cannot take logarithm of zero
3404 select log(1.0, 12.34);
3405 ERROR: division by zero
3406 -- some random tests
3407 select log(1.23e-89, 6.4689e45);
3408 log
3409 ------------------------------------------------------------------------------------------------
3410 -0.5152489207781856983977054971756484879653568168479201885425588841094788842469115325262329756
3411 (1 row)
3413 select log(0.99923, 4.58934e34);
3414 log
3415 ---------------------
3416 -103611.55579544132
3417 (1 row)
3419 select log(1.000016, 8.452010e18);
3420 log
3421 --------------------
3422 2723830.2877097365
3423 (1 row)
3425 select log(3.1954752e47, 9.4792021e-73);
3426 log
3427 -------------------------------------------------------------------------------------
3428 -1.51613372350688302142917386143459361608600157692779164475351842333265418126982165
3429 (1 row)
3431 --
3432 -- Tests for scale()
3433 --
3434 select scale(numeric 'NaN');
3435 scale
3436 -------
3438 (1 row)
3440 select scale(numeric 'inf');
3441 scale
3442 -------
3444 (1 row)
3446 select scale(NULL::numeric);
3447 scale
3448 -------
3450 (1 row)
3452 select scale(1.12);
3453 scale
3454 -------
3455 2
3456 (1 row)
3458 select scale(0);
3459 scale
3460 -------
3461 0
3462 (1 row)
3464 select scale(0.00);
3465 scale
3466 -------
3467 2
3468 (1 row)
3470 select scale(1.12345);
3471 scale
3472 -------
3473 5
3474 (1 row)
3476 select scale(110123.12475871856128);
3477 scale
3478 -------
3479 14
3480 (1 row)
3482 select scale(-1123.12471856128);
3483 scale
3484 -------
3485 11
3486 (1 row)
3488 select scale(-13.000000000000000);
3489 scale
3490 -------
3491 15
3492 (1 row)
3494 --
3495 -- Tests for min_scale()
3496 --
3497 select min_scale(numeric 'NaN') is NULL; -- should be true
3498 ?column?
3499 ----------
3500 t
3501 (1 row)
3503 select min_scale(numeric 'inf') is NULL; -- should be true
3504 ?column?
3505 ----------
3506 t
3507 (1 row)
3509 select min_scale(0); -- no digits
3510 min_scale
3511 -----------
3512 0
3513 (1 row)
3515 select min_scale(0.00); -- no digits again
3516 min_scale
3517 -----------
3518 0
3519 (1 row)
3521 select min_scale(1.0); -- no scale
3522 min_scale
3523 -----------
3524 0
3525 (1 row)
3527 select min_scale(1.1); -- scale 1
3528 min_scale
3529 -----------
3530 1
3531 (1 row)
3533 select min_scale(1.12); -- scale 2
3534 min_scale
3535 -----------
3536 2
3537 (1 row)
3539 select min_scale(1.123); -- scale 3
3540 min_scale
3541 -----------
3542 3
3543 (1 row)
3545 select min_scale(1.1234); -- scale 4, filled digit
3546 min_scale
3547 -----------
3548 4
3549 (1 row)
3551 select min_scale(1.12345); -- scale 5, 2 NDIGITS
3552 min_scale
3553 -----------
3554 5
3555 (1 row)
3557 select min_scale(1.1000); -- 1 pos in NDIGITS
3558 min_scale
3559 -----------
3560 1
3561 (1 row)
3563 select min_scale(1e100); -- very big number
3564 min_scale
3565 -----------
3566 0
3567 (1 row)
3569 --
3570 -- Tests for trim_scale()
3571 --
3572 select trim_scale(numeric 'NaN');
3573 trim_scale
3574 ------------
3575 NaN
3576 (1 row)
3578 select trim_scale(numeric 'inf');
3579 trim_scale
3580 ------------
3581 Infinity
3582 (1 row)
3584 select trim_scale(1.120);
3585 trim_scale
3586 ------------
3587 1.12
3588 (1 row)
3590 select trim_scale(0);
3591 trim_scale
3592 ------------
3593 0
3594 (1 row)
3596 select trim_scale(0.00);
3597 trim_scale
3598 ------------
3599 0
3600 (1 row)
3602 select trim_scale(1.1234500);
3603 trim_scale
3604 ------------
3605 1.12345
3606 (1 row)
3608 select trim_scale(110123.12475871856128000);
3609 trim_scale
3610 -----------------------
3611 110123.12475871856128
3612 (1 row)
3614 select trim_scale(-1123.124718561280000000);
3615 trim_scale
3616 -------------------
3617 -1123.12471856128
3618 (1 row)
3620 select trim_scale(-13.00000000000000000000);
3621 trim_scale
3622 ------------
3623 -13
3624 (1 row)
3626 select trim_scale(1e100);
3627 trim_scale
3628 -------------------------------------------------------------------------------------------------------
3629 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
3630 (1 row)
3632 --
3633 -- Tests for SUM()
3634 --
3635 -- cases that need carry propagation
3636 SELECT SUM(9999::numeric) FROM generate_series(1, 100000);
3637 sum
3638 -----------
3639 999900000
3640 (1 row)
3642 SELECT SUM((-9999)::numeric) FROM generate_series(1, 100000);
3643 sum
3644 ------------
3645 -999900000
3646 (1 row)
3648 --
3649 -- Tests for VARIANCE()
3650 --
3651 CREATE TABLE num_variance (a numeric);
3652 INSERT INTO num_variance VALUES (0);
3653 INSERT INTO num_variance VALUES (3e-500);
3654 INSERT INTO num_variance VALUES (-3e-500);
3655 INSERT INTO num_variance VALUES (4e-500 - 1e-16383);
3656 INSERT INTO num_variance VALUES (-4e-500 + 1e-16383);
3657 -- variance is just under 12.5e-1000 and so should round down to 12e-1000
3658 SELECT trim_scale(variance(a) * 1e1000) FROM num_variance;
3659 trim_scale
3660 ------------
3661 12
3662 (1 row)
3664 -- check that parallel execution produces the same result
3665 BEGIN;
3666 ALTER TABLE num_variance SET (parallel_workers = 4);
3667 SET LOCAL parallel_setup_cost = 0;
3668 SET LOCAL max_parallel_workers_per_gather = 4;
3669 SELECT trim_scale(variance(a) * 1e1000) FROM num_variance;
3670 trim_scale
3671 ------------
3672 12
3673 (1 row)
3675 ROLLBACK;
3676 -- case where sum of squares would overflow but variance does not
3677 DELETE FROM num_variance;
3678 INSERT INTO num_variance SELECT 9e131071 + x FROM generate_series(1, 5) x;
3679 SELECT variance(a) FROM num_variance;
3680 variance
3681 --------------------
3682 2.5000000000000000
3683 (1 row)
3685 -- check that parallel execution produces the same result
3686 BEGIN;
3687 ALTER TABLE num_variance SET (parallel_workers = 4);
3688 SET LOCAL parallel_setup_cost = 0;
3689 SET LOCAL max_parallel_workers_per_gather = 4;
3690 SELECT variance(a) FROM num_variance;
3691 variance
3692 --------------------
3693 2.5000000000000000
3694 (1 row)
3696 ROLLBACK;
3697 DROP TABLE num_variance;
3698 --
3699 -- Tests for GCD()
3700 --
3701 SELECT a, b, gcd(a, b), gcd(a, -b), gcd(-b, a), gcd(-b, -a)
3702 FROM (VALUES (0::numeric, 0::numeric),
3703 (0::numeric, numeric 'NaN'),
3704 (0::numeric, 46375::numeric),
3705 (433125::numeric, 46375::numeric),
3706 (43312.5::numeric, 4637.5::numeric),
3707 (4331.250::numeric, 463.75000::numeric),
3708 ('inf', '0'),
3709 ('inf', '42'),
3710 ('inf', 'inf')
3711 ) AS v(a, b);
3712 a | b | gcd | gcd | gcd | gcd
3713 ----------+-----------+---------+---------+---------+---------
3714 0 | 0 | 0 | 0 | 0 | 0
3715 0 | NaN | NaN | NaN | NaN | NaN
3716 0 | 46375 | 46375 | 46375 | 46375 | 46375
3717 433125 | 46375 | 875 | 875 | 875 | 875
3718 43312.5 | 4637.5 | 87.5 | 87.5 | 87.5 | 87.5
3719 4331.250 | 463.75000 | 8.75000 | 8.75000 | 8.75000 | 8.75000
3720 Infinity | 0 | NaN | NaN | NaN | NaN
3721 Infinity | 42 | NaN | NaN | NaN | NaN
3722 Infinity | Infinity | NaN | NaN | NaN | NaN
3723 (9 rows)
3725 --
3726 -- Tests for LCM()
3727 --
3728 SELECT a,b, lcm(a, b), lcm(a, -b), lcm(-b, a), lcm(-b, -a)
3729 FROM (VALUES (0::numeric, 0::numeric),
3730 (0::numeric, numeric 'NaN'),
3731 (0::numeric, 13272::numeric),
3732 (13272::numeric, 13272::numeric),
3733 (423282::numeric, 13272::numeric),
3734 (42328.2::numeric, 1327.2::numeric),
3735 (4232.820::numeric, 132.72000::numeric),
3736 ('inf', '0'),
3737 ('inf', '42'),
3738 ('inf', 'inf')
3739 ) AS v(a, b);
3740 a | b | lcm | lcm | lcm | lcm
3741 ----------+-----------+--------------+--------------+--------------+--------------
3742 0 | 0 | 0 | 0 | 0 | 0
3743 0 | NaN | NaN | NaN | NaN | NaN
3744 0 | 13272 | 0 | 0 | 0 | 0
3745 13272 | 13272 | 13272 | 13272 | 13272 | 13272
3746 423282 | 13272 | 11851896 | 11851896 | 11851896 | 11851896
3747 42328.2 | 1327.2 | 1185189.6 | 1185189.6 | 1185189.6 | 1185189.6
3748 4232.820 | 132.72000 | 118518.96000 | 118518.96000 | 118518.96000 | 118518.96000
3749 Infinity | 0 | NaN | NaN | NaN | NaN
3750 Infinity | 42 | NaN | NaN | NaN | NaN
3751 Infinity | Infinity | NaN | NaN | NaN | NaN
3752 (10 rows)
3754 SELECT lcm(9999 * (10::numeric)^131068 + (10::numeric^131068 - 1), 2); -- overflow
3755 ERROR: value overflows numeric format
3756 --
3757 -- Tests for factorial
3758 --
3759 SELECT factorial(4);
3760 factorial
3761 -----------
3762 24
3763 (1 row)
3765 SELECT factorial(15);
3766 factorial
3767 ---------------
3768 1307674368000
3769 (1 row)
3771 SELECT factorial(100000);
3772 ERROR: value overflows numeric format
3773 SELECT factorial(0);
3774 factorial
3775 -----------
3776 1
3777 (1 row)
3779 SELECT factorial(-4);
3780 ERROR: factorial of a negative number is undefined
3781 --
3782 -- Tests for pg_lsn()
3783 --
3784 SELECT pg_lsn(23783416::numeric);
3785 pg_lsn
3786 -----------
3787 0/16AE7F8
3788 (1 row)
3790 SELECT pg_lsn(0::numeric);
3791 pg_lsn
3792 --------
3793 0/0
3794 (1 row)
3796 SELECT pg_lsn(18446744073709551615::numeric);
3797 pg_lsn
3798 -------------------
3799 FFFFFFFF/FFFFFFFF
3800 (1 row)
3802 SELECT pg_lsn(-1::numeric);
3803 ERROR: pg_lsn out of range
3804 SELECT pg_lsn(18446744073709551616::numeric);
3805 ERROR: pg_lsn out of range
3806 SELECT pg_lsn('NaN'::numeric);
3807 ERROR: cannot convert NaN to pg_lsn