| blob | efbb22a43f3123c7b5801f21aacfc1997aeb9cff |
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.6400000000000000
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 -- Testing for width_bucket(). For convenience, we test both the
1350 -- numeric and float8 versions of the function in this file.
1351 -- errors
1352 SELECT width_bucket(5.0, 3.0, 4.0, 0);
1353 ERROR: count must be greater than zero
1354 SELECT width_bucket(5.0, 3.0, 4.0, -5);
1355 ERROR: count must be greater than zero
1356 SELECT width_bucket(3.5, 3.0, 3.0, 888);
1357 ERROR: lower bound cannot equal upper bound
1358 SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, 0);
1359 ERROR: count must be greater than zero
1360 SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, -5);
1361 ERROR: count must be greater than zero
1362 SELECT width_bucket(3.5::float8, 3.0::float8, 3.0::float8, 888);
1363 ERROR: lower bound cannot equal upper bound
1364 SELECT width_bucket('NaN', 3.0, 4.0, 888);
1365 ERROR: operand, lower bound, and upper bound cannot be NaN
1366 SELECT width_bucket(0::float8, 'NaN', 4.0::float8, 888);
1367 ERROR: operand, lower bound, and upper bound cannot be NaN
1368 SELECT width_bucket(2.0, 3.0, '-inf', 888);
1369 ERROR: lower and upper bounds must be finite
1370 SELECT width_bucket(0::float8, '-inf', 4.0::float8, 888);
1371 ERROR: lower and upper bounds must be finite
1372 -- normal operation
1373 CREATE TABLE width_bucket_test (operand_num numeric, operand_f8 float8);
1374 COPY width_bucket_test (operand_num) FROM stdin;
1375 UPDATE width_bucket_test SET operand_f8 = operand_num::float8;
1376 SELECT
1377 operand_num,
1378 width_bucket(operand_num, 0, 10, 5) AS wb_1,
1379 width_bucket(operand_f8, 0, 10, 5) AS wb_1f,
1380 width_bucket(operand_num, 10, 0, 5) AS wb_2,
1381 width_bucket(operand_f8, 10, 0, 5) AS wb_2f,
1382 width_bucket(operand_num, 2, 8, 4) AS wb_3,
1383 width_bucket(operand_f8, 2, 8, 4) AS wb_3f,
1384 width_bucket(operand_num, 5.0, 5.5, 20) AS wb_4,
1385 width_bucket(operand_f8, 5.0, 5.5, 20) AS wb_4f,
1386 width_bucket(operand_num, -25, 25, 10) AS wb_5,
1387 width_bucket(operand_f8, -25, 25, 10) AS wb_5f
1388 FROM width_bucket_test;
1389 operand_num | wb_1 | wb_1f | wb_2 | wb_2f | wb_3 | wb_3f | wb_4 | wb_4f | wb_5 | wb_5f
1390 ------------------+------+-------+------+-------+------+-------+------+-------+------+-------
1391 -5.2 | 0 | 0 | 6 | 6 | 0 | 0 | 0 | 0 | 4 | 4
1392 -0.0000000001 | 0 | 0 | 6 | 6 | 0 | 0 | 0 | 0 | 5 | 5
1393 0.000000000001 | 1 | 1 | 5 | 5 | 0 | 0 | 0 | 0 | 6 | 6
1394 1 | 1 | 1 | 5 | 5 | 0 | 0 | 0 | 0 | 6 | 6
1395 1.99999999999999 | 1 | 1 | 5 | 5 | 0 | 0 | 0 | 0 | 6 | 6
1396 2 | 2 | 2 | 5 | 5 | 1 | 1 | 0 | 0 | 6 | 6
1397 2.00000000000001 | 2 | 2 | 4 | 4 | 1 | 1 | 0 | 0 | 6 | 6
1398 3 | 2 | 2 | 4 | 4 | 1 | 1 | 0 | 0 | 6 | 6
1399 4 | 3 | 3 | 4 | 4 | 2 | 2 | 0 | 0 | 6 | 6
1400 4.5 | 3 | 3 | 3 | 3 | 2 | 2 | 0 | 0 | 6 | 6
1401 5 | 3 | 3 | 3 | 3 | 3 | 3 | 1 | 1 | 7 | 7
1402 5.5 | 3 | 3 | 3 | 3 | 3 | 3 | 21 | 21 | 7 | 7
1403 6 | 4 | 4 | 3 | 3 | 3 | 3 | 21 | 21 | 7 | 7
1404 7 | 4 | 4 | 2 | 2 | 4 | 4 | 21 | 21 | 7 | 7
1405 8 | 5 | 5 | 2 | 2 | 5 | 5 | 21 | 21 | 7 | 7
1406 9 | 5 | 5 | 1 | 1 | 5 | 5 | 21 | 21 | 7 | 7
1407 9.99999999999999 | 5 | 5 | 1 | 1 | 5 | 5 | 21 | 21 | 7 | 7
1408 10 | 6 | 6 | 1 | 1 | 5 | 5 | 21 | 21 | 8 | 8
1409 10.0000000000001 | 6 | 6 | 0 | 0 | 5 | 5 | 21 | 21 | 8 | 8
1410 (19 rows)
1412 -- Check positive and negative infinity: we require
1413 -- finite bucket bounds, but allow an infinite operand
1414 SELECT width_bucket(0.0::numeric, 'Infinity'::numeric, 5, 10); -- error
1415 ERROR: lower and upper bounds must be finite
1416 SELECT width_bucket(0.0::numeric, 5, '-Infinity'::numeric, 20); -- error
1417 ERROR: lower and upper bounds must be finite
1418 SELECT width_bucket('Infinity'::numeric, 1, 10, 10),
1419 width_bucket('-Infinity'::numeric, 1, 10, 10);
1420 width_bucket | width_bucket
1421 --------------+--------------
1422 11 | 0
1423 (1 row)
1425 SELECT width_bucket(0.0::float8, 'Infinity'::float8, 5, 10); -- error
1426 ERROR: lower and upper bounds must be finite
1427 SELECT width_bucket(0.0::float8, 5, '-Infinity'::float8, 20); -- error
1428 ERROR: lower and upper bounds must be finite
1429 SELECT width_bucket('Infinity'::float8, 1, 10, 10),
1430 width_bucket('-Infinity'::float8, 1, 10, 10);
1431 width_bucket | width_bucket
1432 --------------+--------------
1433 11 | 0
1434 (1 row)
1436 DROP TABLE width_bucket_test;
1437 -- Simple test for roundoff error when results should be exact
1438 SELECT x, width_bucket(x::float8, 10, 100, 9) as flt,
1439 width_bucket(x::numeric, 10, 100, 9) as num
1440 FROM generate_series(0, 110, 10) x;
1441 x | flt | num
1442 -----+-----+-----
1443 0 | 0 | 0
1444 10 | 1 | 1
1445 20 | 2 | 2
1446 30 | 3 | 3
1447 40 | 4 | 4
1448 50 | 5 | 5
1449 60 | 6 | 6
1450 70 | 7 | 7
1451 80 | 8 | 8
1452 90 | 9 | 9
1453 100 | 10 | 10
1454 110 | 10 | 10
1455 (12 rows)
1457 SELECT x, width_bucket(x::float8, 100, 10, 9) as flt,
1458 width_bucket(x::numeric, 100, 10, 9) as num
1459 FROM generate_series(0, 110, 10) x;
1460 x | flt | num
1461 -----+-----+-----
1462 0 | 10 | 10
1463 10 | 10 | 10
1464 20 | 9 | 9
1465 30 | 8 | 8
1466 40 | 7 | 7
1467 50 | 6 | 6
1468 60 | 5 | 5
1469 70 | 4 | 4
1470 80 | 3 | 3
1471 90 | 2 | 2
1472 100 | 1 | 1
1473 110 | 0 | 0
1474 (12 rows)
1476 --
1477 -- TO_CHAR()
1478 --
1479 SELECT to_char(val, '9G999G999G999G999G999')
1480 FROM num_data;
1481 to_char
1482 ------------------------
1483 0
1484 0
1485 -34,338,492
1486 4
1487 7,799,461
1488 16,397
1489 93,902
1490 -83,028,485
1491 74,881
1492 -24,926,804
1493 (10 rows)
1495 SELECT to_char(val, '9G999G999G999G999G999D999G999G999G999G999')
1496 FROM num_data;
1497 to_char
1498 --------------------------------------------
1499 .000,000,000,000,000
1500 .000,000,000,000,000
1501 -34,338,492.215,397,047,000,000
1502 4.310,000,000,000,000
1503 7,799,461.411,900,000,000,000
1504 16,397.038,491,000,000,000
1505 93,901.577,630,260,000,000
1506 -83,028,485.000,000,000,000,000
1507 74,881.000,000,000,000,000
1508 -24,926,804.045,047,420,000,000
1509 (10 rows)
1511 SELECT to_char(val, '9999999999999999.999999999999999PR')
1512 FROM num_data;
1513 to_char
1514 ------------------------------------
1515 .000000000000000
1516 .000000000000000
1517 <34338492.215397047000000>
1518 4.310000000000000
1519 7799461.411900000000000
1520 16397.038491000000000
1521 93901.577630260000000
1522 <83028485.000000000000000>
1523 74881.000000000000000
1524 <24926804.045047420000000>
1525 (10 rows)
1527 SELECT to_char(val, '9999999999999999.999999999999999S')
1528 FROM num_data;
1529 to_char
1530 -----------------------------------
1531 .000000000000000+
1532 .000000000000000+
1533 34338492.215397047000000-
1534 4.310000000000000+
1535 7799461.411900000000000+
1536 16397.038491000000000+
1537 93901.577630260000000+
1538 83028485.000000000000000-
1539 74881.000000000000000+
1540 24926804.045047420000000-
1541 (10 rows)
1543 SELECT to_char(val, 'MI9999999999999999.999999999999999') FROM num_data;
1544 to_char
1545 -----------------------------------
1546 .000000000000000
1547 .000000000000000
1548 - 34338492.215397047000000
1549 4.310000000000000
1550 7799461.411900000000000
1551 16397.038491000000000
1552 93901.577630260000000
1553 - 83028485.000000000000000
1554 74881.000000000000000
1555 - 24926804.045047420000000
1556 (10 rows)
1558 SELECT to_char(val, 'FMS9999999999999999.999999999999999') FROM num_data;
1559 to_char
1560 ---------------------
1561 +0.
1562 +0.
1563 -34338492.215397047
1564 +4.31
1565 +7799461.4119
1566 +16397.038491
1567 +93901.57763026
1568 -83028485.
1569 +74881.
1570 -24926804.04504742
1571 (10 rows)
1573 SELECT to_char(val, 'FM9999999999999999.999999999999999THPR') FROM num_data;
1574 to_char
1575 ----------------------
1576 0.
1577 0.
1578 <34338492.215397047>
1579 4.31
1580 7799461.4119
1581 16397.038491
1582 93901.57763026
1583 <83028485.>
1584 74881.
1585 <24926804.04504742>
1586 (10 rows)
1588 SELECT to_char(val, 'SG9999999999999999.999999999999999th') FROM num_data;
1589 to_char
1590 -----------------------------------
1591 + .000000000000000
1592 + .000000000000000
1593 - 34338492.215397047000000
1594 + 4.310000000000000
1595 + 7799461.411900000000000
1596 + 16397.038491000000000
1597 + 93901.577630260000000
1598 - 83028485.000000000000000
1599 + 74881.000000000000000
1600 - 24926804.045047420000000
1601 (10 rows)
1603 SELECT to_char(val, '0999999999999999.999999999999999') FROM num_data;
1604 to_char
1605 -----------------------------------
1606 0000000000000000.000000000000000
1607 0000000000000000.000000000000000
1608 -0000000034338492.215397047000000
1609 0000000000000004.310000000000000
1610 0000000007799461.411900000000000
1611 0000000000016397.038491000000000
1612 0000000000093901.577630260000000
1613 -0000000083028485.000000000000000
1614 0000000000074881.000000000000000
1615 -0000000024926804.045047420000000
1616 (10 rows)
1618 SELECT to_char(val, 'S0999999999999999.999999999999999') FROM num_data;
1619 to_char
1620 -----------------------------------
1621 +0000000000000000.000000000000000
1622 +0000000000000000.000000000000000
1623 -0000000034338492.215397047000000
1624 +0000000000000004.310000000000000
1625 +0000000007799461.411900000000000
1626 +0000000000016397.038491000000000
1627 +0000000000093901.577630260000000
1628 -0000000083028485.000000000000000
1629 +0000000000074881.000000000000000
1630 -0000000024926804.045047420000000
1631 (10 rows)
1633 SELECT to_char(val, 'FM0999999999999999.999999999999999') FROM num_data;
1634 to_char
1635 -----------------------------
1636 0000000000000000.
1637 0000000000000000.
1638 -0000000034338492.215397047
1639 0000000000000004.31
1640 0000000007799461.4119
1641 0000000000016397.038491
1642 0000000000093901.57763026
1643 -0000000083028485.
1644 0000000000074881.
1645 -0000000024926804.04504742
1646 (10 rows)
1648 SELECT to_char(val, 'FM9999999999999999.099999999999999') FROM num_data;
1649 to_char
1650 ---------------------
1651 .0
1652 .0
1653 -34338492.215397047
1654 4.31
1655 7799461.4119
1656 16397.038491
1657 93901.57763026
1658 -83028485.0
1659 74881.0
1660 -24926804.04504742
1661 (10 rows)
1663 SELECT to_char(val, 'FM9999999999990999.990999999999999') FROM num_data;
1664 to_char
1665 ---------------------
1666 0000.000
1667 0000.000
1668 -34338492.215397047
1669 0004.310
1670 7799461.4119
1671 16397.038491
1672 93901.57763026
1673 -83028485.000
1674 74881.000
1675 -24926804.04504742
1676 (10 rows)
1678 SELECT to_char(val, 'FM0999999999999999.999909999999999') FROM num_data;
1679 to_char
1680 -----------------------------
1681 0000000000000000.00000
1682 0000000000000000.00000
1683 -0000000034338492.215397047
1684 0000000000000004.31000
1685 0000000007799461.41190
1686 0000000000016397.038491
1687 0000000000093901.57763026
1688 -0000000083028485.00000
1689 0000000000074881.00000
1690 -0000000024926804.04504742
1691 (10 rows)
1693 SELECT to_char(val, 'FM9999999990999999.099999999999999') FROM num_data;
1694 to_char
1695 ---------------------
1696 0000000.0
1697 0000000.0
1698 -34338492.215397047
1699 0000004.31
1700 7799461.4119
1701 0016397.038491
1702 0093901.57763026
1703 -83028485.0
1704 0074881.0
1705 -24926804.04504742
1706 (10 rows)
1708 SELECT to_char(val, 'L9999999999999999.099999999999999') FROM num_data;
1709 to_char
1710 ------------------------------------
1711 .000000000000000
1712 .000000000000000
1713 -34338492.215397047000000
1714 4.310000000000000
1715 7799461.411900000000000
1716 16397.038491000000000
1717 93901.577630260000000
1718 -83028485.000000000000000
1719 74881.000000000000000
1720 -24926804.045047420000000
1721 (10 rows)
1723 SELECT to_char(val, 'FM9999999999999999.99999999999999') FROM num_data;
1724 to_char
1725 ---------------------
1726 0.
1727 0.
1728 -34338492.215397047
1729 4.31
1730 7799461.4119
1731 16397.038491
1732 93901.57763026
1733 -83028485.
1734 74881.
1735 -24926804.04504742
1736 (10 rows)
1738 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;
1739 to_char
1740 -----------------------------------------------------------------------
1741 +. 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1742 +. 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1743 -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
1744 +4 . 3 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1745 +7 7 9 9 4 6 1 . 4 1 1 9 0 0 0 0 0 0 0 0 0 0 0 0 0
1746 +1 6 3 9 7 . 0 3 8 4 9 1 0 0 0 0 0 0 0 0 0 0 0
1747 +9 3 9 0 1 . 5 7 7 6 3 0 2 6 0 0 0 0 0 0 0 0 0
1748 -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
1749 +7 4 8 8 1 . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1750 -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
1751 (10 rows)
1753 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;
1754 to_char
1755 -------------------------------------------------------
1756 +0 .
1757 +0 .
1758 -3 4 3 3 8 4 9 2 . 2 1 5 3 9 7 0 4 7
1759 +4 . 3 1
1760 +7 7 9 9 4 6 1 . 4 1 1 9
1761 +1 6 3 9 7 . 0 3 8 4 9 1
1762 +9 3 9 0 1 . 5 7 7 6 3 0 2 6
1763 -8 3 0 2 8 4 8 5 .
1764 +7 4 8 8 1 .
1765 -2 4 9 2 6 8 0 4 . 0 4 5 0 4 7 4 2
1766 (10 rows)
1768 SELECT to_char(val, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM num_data;
1769 to_char
1770 -----------------------------------------------------------
1771 text 9999 "text between quote marks" 0
1772 text 9999 "text between quote marks" 0
1773 text -3 9999 433 "text between quote marks" 8492
1774 text 9999 "text between quote marks" 4
1775 text 9999 779 "text between quote marks" 9461
1776 text 9999 1 "text between quote marks" 6397
1777 text 9999 9 "text between quote marks" 3902
1778 text -8 9999 302 "text between quote marks" 8485
1779 text 9999 7 "text between quote marks" 4881
1780 text -2 9999 492 "text between quote marks" 6804
1781 (10 rows)
1783 SELECT to_char(val, '999999SG9999999999') FROM num_data;
1784 to_char
1785 -------------------
1786 + 0
1787 + 0
1788 - 34338492
1789 + 4
1790 + 7799461
1791 + 16397
1792 + 93902
1793 - 83028485
1794 + 74881
1795 - 24926804
1796 (10 rows)
1798 SELECT to_char(val, 'FM9999999999999999.999999999999999') FROM num_data;
1799 to_char
1800 ---------------------
1801 0.
1802 0.
1803 -34338492.215397047
1804 4.31
1805 7799461.4119
1806 16397.038491
1807 93901.57763026
1808 -83028485.
1809 74881.
1810 -24926804.04504742
1811 (10 rows)
1813 SELECT to_char(val, '9.999EEEE') FROM num_data;
1814 to_char
1815 ------------
1816 0.000e+00
1817 0.000e+00
1818 -3.434e+07
1819 4.310e+00
1820 7.799e+06
1821 1.640e+04
1822 9.390e+04
1823 -8.303e+07
1824 7.488e+04
1825 -2.493e+07
1826 (10 rows)
1828 WITH v(val) AS
1829 (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan'))
1830 SELECT val,
1831 to_char(val, '9.999EEEE') as numeric,
1832 to_char(val::float8, '9.999EEEE') as float8,
1833 to_char(val::float4, '9.999EEEE') as float4
1834 FROM v;
1835 val | numeric | float8 | float4
1836 ------------+------------+------------+------------
1837 0 | 0.000e+00 | 0.000e+00 | 0.000e+00
1838 -4.2 | -4.200e+00 | -4.200e+00 | -4.200e+00
1839 4200000000 | 4.200e+09 | 4.200e+09 | 4.200e+09
1840 0.000012 | 1.200e-05 | 1.200e-05 | 1.200e-05
1841 Infinity | #.####### | #.####### | #.#######
1842 -Infinity | #.####### | #.####### | #.#######
1843 NaN | #.####### | #.####### | #.#######
1844 (7 rows)
1846 WITH v(exp) AS
1847 (VALUES(-16379),(-16378),(-1234),(-789),(-45),(-5),(-4),(-3),(-2),(-1),(0),
1848 (1),(2),(3),(4),(5),(38),(275),(2345),(45678),(131070),(131071))
1849 SELECT exp,
1850 to_char(('1.2345e'||exp)::numeric, '9.999EEEE') as numeric
1851 FROM v;
1852 exp | numeric
1853 --------+----------------
1854 -16379 | 1.235e-16379
1855 -16378 | 1.235e-16378
1856 -1234 | 1.235e-1234
1857 -789 | 1.235e-789
1858 -45 | 1.235e-45
1859 -5 | 1.235e-05
1860 -4 | 1.235e-04
1861 -3 | 1.235e-03
1862 -2 | 1.235e-02
1863 -1 | 1.235e-01
1864 0 | 1.235e+00
1865 1 | 1.235e+01
1866 2 | 1.235e+02
1867 3 | 1.235e+03
1868 4 | 1.235e+04
1869 5 | 1.235e+05
1870 38 | 1.235e+38
1871 275 | 1.235e+275
1872 2345 | 1.235e+2345
1873 45678 | 1.235e+45678
1874 131070 | 1.235e+131070
1875 131071 | 1.235e+131071
1876 (22 rows)
1878 WITH v(val) AS
1879 (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan'))
1880 SELECT val,
1881 to_char(val, 'MI9999999999.99') as numeric,
1882 to_char(val::float8, 'MI9999999999.99') as float8,
1883 to_char(val::float4, 'MI9999999999.99') as float4
1884 FROM v;
1885 val | numeric | float8 | float4
1886 ------------+----------------+----------------+----------------
1887 0 | .00 | .00 | .00
1888 -4.2 | - 4.20 | - 4.20 | - 4.20
1889 4200000000 | 4200000000.00 | 4200000000.00 | 4200000000
1890 0.000012 | .00 | .00 | .00
1891 Infinity | Infinity | Infinity | Infinity
1892 -Infinity | - Infinity | - Infinity | - Infinity
1893 NaN | NaN | NaN | NaN
1894 (7 rows)
1896 WITH v(val) AS
1897 (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan'))
1898 SELECT val,
1899 to_char(val, 'MI99.99') as numeric,
1900 to_char(val::float8, 'MI99.99') as float8,
1901 to_char(val::float4, 'MI99.99') as float4
1902 FROM v;
1903 val | numeric | float8 | float4
1904 ------------+---------+--------+--------
1905 0 | .00 | .00 | .00
1906 -4.2 | - 4.20 | - 4.20 | - 4.20
1907 4200000000 | ##.## | ##.## | ##.
1908 0.000012 | .00 | .00 | .00
1909 Infinity | ##.## | ##.## | ##.
1910 -Infinity | -##.## | -##.## | -##.
1911 NaN | ##.## | ##.## | ##.##
1912 (7 rows)
1914 SELECT to_char('100'::numeric, 'FM999.9');
1915 to_char
1916 ---------
1917 100.
1918 (1 row)
1920 SELECT to_char('100'::numeric, 'FM999.');
1921 to_char
1922 ---------
1923 100
1924 (1 row)
1926 SELECT to_char('100'::numeric, 'FM999');
1927 to_char
1928 ---------
1929 100
1930 (1 row)
1932 -- Check parsing of literal text in a format string
1933 SELECT to_char('100'::numeric, 'foo999');
1934 to_char
1935 ---------
1936 foo 100
1937 (1 row)
1939 SELECT to_char('100'::numeric, 'f\oo999');
1940 to_char
1941 ----------
1942 f\oo 100
1943 (1 row)
1945 SELECT to_char('100'::numeric, 'f\\oo999');
1946 to_char
1947 -----------
1948 f\\oo 100
1949 (1 row)
1951 SELECT to_char('100'::numeric, 'f\"oo999');
1952 to_char
1953 ----------
1954 f"oo 100
1955 (1 row)
1957 SELECT to_char('100'::numeric, 'f\\"oo999');
1958 to_char
1959 -----------
1960 f\"oo 100
1961 (1 row)
1963 SELECT to_char('100'::numeric, 'f"ool"999');
1964 to_char
1965 ----------
1966 fool 100
1967 (1 row)
1969 SELECT to_char('100'::numeric, 'f"\ool"999');
1970 to_char
1971 ----------
1972 fool 100
1973 (1 row)
1975 SELECT to_char('100'::numeric, 'f"\\ool"999');
1976 to_char
1977 -----------
1978 f\ool 100
1979 (1 row)
1981 SELECT to_char('100'::numeric, 'f"ool\"999');
1982 to_char
1983 ----------
1984 fool"999
1985 (1 row)
1987 SELECT to_char('100'::numeric, 'f"ool\\"999');
1988 to_char
1989 -----------
1990 fool\ 100
1991 (1 row)
1993 -- TO_NUMBER()
1994 --
1995 SET lc_numeric = 'C';
1996 SELECT to_number('-34,338,492', '99G999G999');
1997 to_number
1998 -----------
1999 -34338492
2000 (1 row)
2002 SELECT to_number('-34,338,492.654,878', '99G999G999D999G999');
2003 to_number
2004 ------------------
2005 -34338492.654878
2006 (1 row)
2008 SELECT to_number('<564646.654564>', '999999.999999PR');
2009 to_number
2010 ----------------
2011 -564646.654564
2012 (1 row)
2014 SELECT to_number('0.00001-', '9.999999S');
2015 to_number
2016 -----------
2017 -0.00001
2018 (1 row)
2020 SELECT to_number('5.01-', 'FM9.999999S');
2021 to_number
2022 -----------
2023 -5.01
2024 (1 row)
2026 SELECT to_number('5.01-', 'FM9.999999MI');
2027 to_number
2028 -----------
2029 -5.01
2030 (1 row)
2032 SELECT to_number('5 4 4 4 4 8 . 7 8', '9 9 9 9 9 9 . 9 9');
2033 to_number
2034 -----------
2035 544448.78
2036 (1 row)
2038 SELECT to_number('.01', 'FM9.99');
2039 to_number
2040 -----------
2041 0.01
2042 (1 row)
2044 SELECT to_number('.0', '99999999.99999999');
2045 to_number
2046 -----------
2047 0.0
2048 (1 row)
2050 SELECT to_number('0', '99.99');
2051 to_number
2052 -----------
2053 0
2054 (1 row)
2056 SELECT to_number('.-01', 'S99.99');
2057 to_number
2058 -----------
2059 -0.01
2060 (1 row)
2062 SELECT to_number('.01-', '99.99S');
2063 to_number
2064 -----------
2065 -0.01
2066 (1 row)
2068 SELECT to_number(' . 0 1-', ' 9 9 . 9 9 S');
2069 to_number
2070 -----------
2071 -0.01
2072 (1 row)
2074 SELECT to_number('34,50','999,99');
2075 to_number
2076 -----------
2077 3450
2078 (1 row)
2080 SELECT to_number('123,000','999G');
2081 to_number
2082 -----------
2083 123
2084 (1 row)
2086 SELECT to_number('123456','999G999');
2087 to_number
2088 -----------
2089 123456
2090 (1 row)
2092 SELECT to_number('$1234.56','L9,999.99');
2093 to_number
2094 -----------
2095 1234.56
2096 (1 row)
2098 SELECT to_number('$1234.56','L99,999.99');
2099 to_number
2100 -----------
2101 1234.56
2102 (1 row)
2104 SELECT to_number('$1,234.56','L99,999.99');
2105 to_number
2106 -----------
2107 1234.56
2108 (1 row)
2110 SELECT to_number('1234.56','L99,999.99');
2111 to_number
2112 -----------
2113 1234.56
2114 (1 row)
2116 SELECT to_number('1,234.56','L99,999.99');
2117 to_number
2118 -----------
2119 1234.56
2120 (1 row)
2122 SELECT to_number('42nd', '99th');
2123 to_number
2124 -----------
2125 42
2126 (1 row)
2128 RESET lc_numeric;
2129 --
2130 -- Input syntax
2131 --
2132 CREATE TABLE num_input_test (n1 numeric);
2133 -- good inputs
2134 INSERT INTO num_input_test(n1) VALUES (' 123');
2135 INSERT INTO num_input_test(n1) VALUES (' 3245874 ');
2136 INSERT INTO num_input_test(n1) VALUES (' -93853');
2137 INSERT INTO num_input_test(n1) VALUES ('555.50');
2138 INSERT INTO num_input_test(n1) VALUES ('-555.50');
2139 INSERT INTO num_input_test(n1) VALUES ('NaN ');
2140 INSERT INTO num_input_test(n1) VALUES (' nan');
2141 INSERT INTO num_input_test(n1) VALUES (' inf ');
2142 INSERT INTO num_input_test(n1) VALUES (' +inf ');
2143 INSERT INTO num_input_test(n1) VALUES (' -inf ');
2144 INSERT INTO num_input_test(n1) VALUES (' Infinity ');
2145 INSERT INTO num_input_test(n1) VALUES (' +inFinity ');
2146 INSERT INTO num_input_test(n1) VALUES (' -INFINITY ');
2147 -- bad inputs
2148 INSERT INTO num_input_test(n1) VALUES (' ');
2149 ERROR: invalid input syntax for type numeric: " "
2150 LINE 1: INSERT INTO num_input_test(n1) VALUES (' ');
2151 ^
2152 INSERT INTO num_input_test(n1) VALUES (' 1234 %');
2153 ERROR: invalid input syntax for type numeric: " 1234 %"
2154 LINE 1: INSERT INTO num_input_test(n1) VALUES (' 1234 %');
2155 ^
2156 INSERT INTO num_input_test(n1) VALUES ('xyz');
2157 ERROR: invalid input syntax for type numeric: "xyz"
2158 LINE 1: INSERT INTO num_input_test(n1) VALUES ('xyz');
2159 ^
2160 INSERT INTO num_input_test(n1) VALUES ('- 1234');
2161 ERROR: invalid input syntax for type numeric: "- 1234"
2162 LINE 1: INSERT INTO num_input_test(n1) VALUES ('- 1234');
2163 ^
2164 INSERT INTO num_input_test(n1) VALUES ('5 . 0');
2165 ERROR: invalid input syntax for type numeric: "5 . 0"
2166 LINE 1: INSERT INTO num_input_test(n1) VALUES ('5 . 0');
2167 ^
2168 INSERT INTO num_input_test(n1) VALUES ('5. 0 ');
2169 ERROR: invalid input syntax for type numeric: "5. 0 "
2170 LINE 1: INSERT INTO num_input_test(n1) VALUES ('5. 0 ');
2171 ^
2172 INSERT INTO num_input_test(n1) VALUES ('');
2173 ERROR: invalid input syntax for type numeric: ""
2174 LINE 1: INSERT INTO num_input_test(n1) VALUES ('');
2175 ^
2176 INSERT INTO num_input_test(n1) VALUES (' N aN ');
2177 ERROR: invalid input syntax for type numeric: " N aN "
2178 LINE 1: INSERT INTO num_input_test(n1) VALUES (' N aN ');
2179 ^
2180 INSERT INTO num_input_test(n1) VALUES ('+ infinity');
2181 ERROR: invalid input syntax for type numeric: "+ infinity"
2182 LINE 1: INSERT INTO num_input_test(n1) VALUES ('+ infinity');
2183 ^
2184 SELECT * FROM num_input_test;
2185 n1
2186 -----------
2187 123
2188 3245874
2189 -93853
2190 555.50
2191 -555.50
2192 NaN
2193 NaN
2194 Infinity
2195 Infinity
2196 -Infinity
2197 Infinity
2198 Infinity
2199 -Infinity
2200 (13 rows)
2202 --
2203 -- Test precision and scale typemods
2204 --
2205 CREATE TABLE num_typemod_test (
2206 millions numeric(3, -6),
2207 thousands numeric(3, -3),
2208 units numeric(3, 0),
2209 thousandths numeric(3, 3),
2210 millionths numeric(3, 6)
2211 );
2212 \d num_typemod_test
2213 Table "public.num_typemod_test"
2214 Column | Type | Collation | Nullable | Default
2215 -------------+---------------+-----------+----------+---------
2216 millions | numeric(3,-6) | | |
2217 thousands | numeric(3,-3) | | |
2218 units | numeric(3,0) | | |
2219 thousandths | numeric(3,3) | | |
2220 millionths | numeric(3,6) | | |
2222 -- rounding of valid inputs
2223 INSERT INTO num_typemod_test VALUES (123456, 123, 0.123, 0.000123, 0.000000123);
2224 INSERT INTO num_typemod_test VALUES (654321, 654, 0.654, 0.000654, 0.000000654);
2225 INSERT INTO num_typemod_test VALUES (2345678, 2345, 2.345, 0.002345, 0.000002345);
2226 INSERT INTO num_typemod_test VALUES (7654321, 7654, 7.654, 0.007654, 0.000007654);
2227 INSERT INTO num_typemod_test VALUES (12345678, 12345, 12.345, 0.012345, 0.000012345);
2228 INSERT INTO num_typemod_test VALUES (87654321, 87654, 87.654, 0.087654, 0.000087654);
2229 INSERT INTO num_typemod_test VALUES (123456789, 123456, 123.456, 0.123456, 0.000123456);
2230 INSERT INTO num_typemod_test VALUES (987654321, 987654, 987.654, 0.987654, 0.000987654);
2231 INSERT INTO num_typemod_test VALUES ('NaN', 'NaN', 'NaN', 'NaN', 'NaN');
2232 SELECT scale(millions), * FROM num_typemod_test ORDER BY millions;
2233 scale | millions | thousands | units | thousandths | millionths
2234 -------+-----------+-----------+-------+-------------+------------
2235 0 | 0 | 0 | 0 | 0.000 | 0.000000
2236 0 | 1000000 | 1000 | 1 | 0.001 | 0.000001
2237 0 | 2000000 | 2000 | 2 | 0.002 | 0.000002
2238 0 | 8000000 | 8000 | 8 | 0.008 | 0.000008
2239 0 | 12000000 | 12000 | 12 | 0.012 | 0.000012
2240 0 | 88000000 | 88000 | 88 | 0.088 | 0.000088
2241 0 | 123000000 | 123000 | 123 | 0.123 | 0.000123
2242 0 | 988000000 | 988000 | 988 | 0.988 | 0.000988
2243 | NaN | NaN | NaN | NaN | NaN
2244 (9 rows)
2246 -- invalid inputs
2247 INSERT INTO num_typemod_test (millions) VALUES ('inf');
2248 ERROR: numeric field overflow
2249 DETAIL: A field with precision 3, scale -6 cannot hold an infinite value.
2250 INSERT INTO num_typemod_test (millions) VALUES (999500000);
2251 ERROR: numeric field overflow
2252 DETAIL: A field with precision 3, scale -6 must round to an absolute value less than 10^9.
2253 INSERT INTO num_typemod_test (thousands) VALUES (999500);
2254 ERROR: numeric field overflow
2255 DETAIL: A field with precision 3, scale -3 must round to an absolute value less than 10^6.
2256 INSERT INTO num_typemod_test (units) VALUES (999.5);
2257 ERROR: numeric field overflow
2258 DETAIL: A field with precision 3, scale 0 must round to an absolute value less than 10^3.
2259 INSERT INTO num_typemod_test (thousandths) VALUES (0.9995);
2260 ERROR: numeric field overflow
2261 DETAIL: A field with precision 3, scale 3 must round to an absolute value less than 1.
2262 INSERT INTO num_typemod_test (millionths) VALUES (0.0009995);
2263 ERROR: numeric field overflow
2264 DETAIL: A field with precision 3, scale 6 must round to an absolute value less than 10^-3.
2265 --
2266 -- Test some corner cases for multiplication
2267 --
2268 select 4790999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
2269 ?column?
2270 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2271 47909999999999999999999999999999999999999999999999999999999999999999999999999999999999985209000000000000000000000000000000000000000000000000000000000000000000000000000000000001
2272 (1 row)
2274 select 4789999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
2275 ?column?
2276 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2277 47899999999999999999999999999999999999999999999999999999999999999999999999999999999999985210000000000000000000000000000000000000000000000000000000000000000000000000000000000001
2278 (1 row)
2280 select 4770999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
2281 ?column?
2282 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2283 47709999999999999999999999999999999999999999999999999999999999999999999999999999999999985229000000000000000000000000000000000000000000000000000000000000000000000000000000000001
2284 (1 row)
2286 select 4769999999999999999999999999999999999999999999999999999999999999999999999999999999999999 * 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
2287 ?column?
2288 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2289 47699999999999999999999999999999999999999999999999999999999999999999999999999999999999985230000000000000000000000000000000000000000000000000000000000000000000000000000000000001
2290 (1 row)
2292 select trim_scale((0.1 - 2e-16383) * (0.1 - 3e-16383));
2293 trim_scale
2294 ------------
2295 0.01
2296 (1 row)
2298 --
2299 -- Test some corner cases for division
2300 --
2301 select 999999999999999999999::numeric/1000000000000000000000;
2302 ?column?
2303 ------------------------
2304 1.00000000000000000000
2305 (1 row)
2307 select div(999999999999999999999::numeric,1000000000000000000000);
2308 div
2309 -----
2310 0
2311 (1 row)
2313 select mod(999999999999999999999::numeric,1000000000000000000000);
2314 mod
2315 -----------------------
2316 999999999999999999999
2317 (1 row)
2319 select div(-9999999999999999999999::numeric,1000000000000000000000);
2320 div
2321 -----
2322 -9
2323 (1 row)
2325 select mod(-9999999999999999999999::numeric,1000000000000000000000);
2326 mod
2327 ------------------------
2328 -999999999999999999999
2329 (1 row)
2331 select div(-9999999999999999999999::numeric,1000000000000000000000)*1000000000000000000000 + mod(-9999999999999999999999::numeric,1000000000000000000000);
2332 ?column?
2333 -------------------------
2334 -9999999999999999999999
2335 (1 row)
2337 select mod (70.0,70) ;
2338 mod
2339 -----
2340 0.0
2341 (1 row)
2343 select div (70.0,70) ;
2344 div
2345 -----
2346 1
2347 (1 row)
2349 select 70.0 / 70 ;
2350 ?column?
2351 ------------------------
2352 1.00000000000000000000
2353 (1 row)
2355 select 12345678901234567890 % 123;
2356 ?column?
2357 ----------
2358 78
2359 (1 row)
2361 select 12345678901234567890 / 123;
2362 ?column?
2363 --------------------
2364 100371373180768845
2365 (1 row)
2367 select div(12345678901234567890, 123);
2368 div
2369 --------------------
2370 100371373180768844
2371 (1 row)
2373 select div(12345678901234567890, 123) * 123 + 12345678901234567890 % 123;
2374 ?column?
2375 ----------------------
2376 12345678901234567890
2377 (1 row)
2379 --
2380 -- Test some corner cases for square root
2381 --
2382 select sqrt(1.000000000000003::numeric);
2383 sqrt
2384 -------------------
2385 1.000000000000001
2386 (1 row)
2388 select sqrt(1.000000000000004::numeric);
2389 sqrt
2390 -------------------
2391 1.000000000000002
2392 (1 row)
2394 select sqrt(96627521408608.56340355805::numeric);
2395 sqrt
2396 ---------------------
2397 9829929.87811248648
2398 (1 row)
2400 select sqrt(96627521408608.56340355806::numeric);
2401 sqrt
2402 ---------------------
2403 9829929.87811248649
2404 (1 row)
2406 select sqrt(515549506212297735.073688290367::numeric);
2407 sqrt
2408 ------------------------
2409 718017761.766585921184
2410 (1 row)
2412 select sqrt(515549506212297735.073688290368::numeric);
2413 sqrt
2414 ------------------------
2415 718017761.766585921185
2416 (1 row)
2418 select sqrt(8015491789940783531003294973900306::numeric);
2419 sqrt
2420 -------------------
2421 89529278953540017
2422 (1 row)
2424 select sqrt(8015491789940783531003294973900307::numeric);
2425 sqrt
2426 -------------------
2427 89529278953540018
2428 (1 row)
2430 --
2431 -- Test code path for raising to integer powers
2432 --
2433 select 10.0 ^ -2147483648 as rounds_to_zero;
2434 rounds_to_zero
2435 --------------------
2436 0.0000000000000000
2437 (1 row)
2439 select 10.0 ^ -2147483647 as rounds_to_zero;
2440 rounds_to_zero
2441 --------------------
2442 0.0000000000000000
2443 (1 row)
2445 select 10.0 ^ 2147483647 as overflows;
2446 ERROR: value overflows numeric format
2447 select 117743296169.0 ^ 1000000000 as overflows;
2448 ERROR: value overflows numeric format
2449 -- cases that used to return inaccurate results
2450 select 3.789 ^ 21;
2451 ?column?
2452 --------------------------------
2453 1409343026052.8716016316022141
2454 (1 row)
2456 select 3.789 ^ 35;
2457 ?column?
2458 ----------------------------------------
2459 177158169650516670809.3820586142670135
2460 (1 row)
2462 select 1.2 ^ 345;
2463 ?column?
2464 -----------------------------------------------
2465 2077446682327378559843444695.5827049735727869
2466 (1 row)
2468 select 0.12 ^ (-20);
2469 ?column?
2470 --------------------------------------
2471 2608405330458882702.5529619561355838
2472 (1 row)
2474 select 1.000000000123 ^ (-2147483648);
2475 ?column?
2476 --------------------
2477 0.7678656556403084
2478 (1 row)
2480 select coalesce(nullif(0.9999999999 ^ 23300000000000, 0), 0) as rounds_to_zero;
2481 rounds_to_zero
2482 ----------------
2483 0
2484 (1 row)
2486 -- cases that used to error out
2487 select 0.12 ^ (-25);
2488 ?column?
2489 -------------------------------------------
2490 104825960103961013959336.4983657883169110
2491 (1 row)
2493 select 0.5678 ^ (-85);
2494 ?column?
2495 ----------------------------------------
2496 782333637740774446257.7719390061997396
2497 (1 row)
2499 select coalesce(nullif(0.9999999999 ^ 70000000000000, 0), 0) as underflows;
2500 underflows
2501 ------------
2502 0
2503 (1 row)
2505 -- negative base to integer powers
2506 select (-1.0) ^ 2147483646;
2507 ?column?
2508 --------------------
2509 1.0000000000000000
2510 (1 row)
2512 select (-1.0) ^ 2147483647;
2513 ?column?
2514 ---------------------
2515 -1.0000000000000000
2516 (1 row)
2518 select (-1.0) ^ 2147483648;
2519 ?column?
2520 --------------------
2521 1.0000000000000000
2522 (1 row)
2524 select (-1.0) ^ 1000000000000000;
2525 ?column?
2526 --------------------
2527 1.0000000000000000
2528 (1 row)
2530 select (-1.0) ^ 1000000000000001;
2531 ?column?
2532 ---------------------
2533 -1.0000000000000000
2534 (1 row)
2536 --
2537 -- Tests for raising to non-integer powers
2538 --
2539 -- special cases
2540 select 0.0 ^ 0.0;
2541 ?column?
2542 --------------------
2543 1.0000000000000000
2544 (1 row)
2546 select (-12.34) ^ 0.0;
2547 ?column?
2548 --------------------
2549 1.0000000000000000
2550 (1 row)
2552 select 12.34 ^ 0.0;
2553 ?column?
2554 --------------------
2555 1.0000000000000000
2556 (1 row)
2558 select 0.0 ^ 12.34;
2559 ?column?
2560 --------------------
2561 0.0000000000000000
2562 (1 row)
2564 -- NaNs
2565 select 'NaN'::numeric ^ 'NaN'::numeric;
2566 ?column?
2567 ----------
2568 NaN
2569 (1 row)
2571 select 'NaN'::numeric ^ 0;
2572 ?column?
2573 ----------
2574 1
2575 (1 row)
2577 select 'NaN'::numeric ^ 1;
2578 ?column?
2579 ----------
2580 NaN
2581 (1 row)
2583 select 0 ^ 'NaN'::numeric;
2584 ?column?
2585 ----------
2586 NaN
2587 (1 row)
2589 select 1 ^ 'NaN'::numeric;
2590 ?column?
2591 ----------
2592 1
2593 (1 row)
2595 -- invalid inputs
2596 select 0.0 ^ (-12.34);
2597 ERROR: zero raised to a negative power is undefined
2598 select (-12.34) ^ 1.2;
2599 ERROR: a negative number raised to a non-integer power yields a complex result
2600 -- cases that used to generate inaccurate results
2601 select 32.1 ^ 9.8;
2602 ?column?
2603 --------------------
2604 580429286790711.10
2605 (1 row)
2607 select 32.1 ^ (-9.8);
2608 ?column?
2609 ----------------------------------
2610 0.000000000000001722862754788209
2611 (1 row)
2613 select 12.3 ^ 45.6;
2614 ?column?
2615 ------------------------------------------------------
2616 50081010321492803393171165777624533697036806969694.9
2617 (1 row)
2619 select 12.3 ^ (-45.6);
2620 ?column?
2621 ---------------------------------------------------------------------
2622 0.00000000000000000000000000000000000000000000000001996764828785491
2623 (1 row)
2625 -- big test
2626 select 1.234 ^ 5678;
2627 ?column?
2628 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2629 307239295662090741644584872593956173493568238595074141254349565406661439636598896798876823220904084953233015553994854875890890858118656468658643918169805277399402542281777901029346337707622181574346585989613344285010764501017625366742865066948856161360224801370482171458030533346309750557140549621313515752078638620714732831815297168231790779296290266207315344008883935010274044001522606235576584215999260117523114297033944018699691024106823438431754073086813382242140602291215149759520833200152654884259619588924545324.5973362312547382
2630 (1 row)
2632 --
2633 -- Tests for EXP()
2634 --
2635 -- special cases
2636 select exp(0.0);
2637 exp
2638 --------------------
2639 1.0000000000000000
2640 (1 row)
2642 select exp(1.0);
2643 exp
2644 --------------------
2645 2.7182818284590452
2646 (1 row)
2648 select exp(1.0::numeric(71,70));
2649 exp
2650 --------------------------------------------------------------------------
2651 2.7182818284590452353602874713526624977572470936999595749669676277240766
2652 (1 row)
2654 select exp('nan'::numeric);
2655 exp
2656 -----
2657 NaN
2658 (1 row)
2660 select exp('inf'::numeric);
2661 exp
2662 ----------
2663 Infinity
2664 (1 row)
2666 select exp('-inf'::numeric);
2667 exp
2668 -----
2669 0
2670 (1 row)
2672 select coalesce(nullif(exp(-5000::numeric), 0), 0) as rounds_to_zero;
2673 rounds_to_zero
2674 ----------------
2675 0
2676 (1 row)
2678 select coalesce(nullif(exp(-10000::numeric), 0), 0) as underflows;
2679 underflows
2680 ------------
2681 0
2682 (1 row)
2684 -- cases that used to generate inaccurate results
2685 select exp(32.999);
2686 exp
2687 ---------------------
2688 214429043492155.053
2689 (1 row)
2691 select exp(-32.999);
2692 exp
2693 ----------------------------------
2694 0.000000000000004663547361468248
2695 (1 row)
2697 select exp(123.456);
2698 exp
2699 ------------------------------------------------------------
2700 413294435277809344957685441227343146614594393746575438.725
2701 (1 row)
2703 select exp(-123.456);
2704 exp
2705 -------------------------------------------------------------------------
2706 0.000000000000000000000000000000000000000000000000000002419582541264601
2707 (1 row)
2709 -- big test
2710 select exp(1234.5678);
2711 exp
2712 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2713 146549072930959479983482138503979804217622199675223653966270157446954995433819741094410764947112047906012815540251009949604426069672532417736057033099274204598385314594846509975629046864798765888104789074984927709616261452461385220475510438783429612447831614003668421849727379202555580791042606170523016207262965336641214601082882495255771621327088265411334088968112458492660609809762865582162764292604697957813514621259353683899630997077707406305730694385703091201347848855199354307506425820147289848677003277208302716466011827836279231.9667
2714 (1 row)
2716 --
2717 -- Tests for generate_series
2718 --
2719 select * from generate_series(0.0::numeric, 4.0::numeric);
2720 generate_series
2721 -----------------
2722 0.0
2723 1.0
2724 2.0
2725 3.0
2726 4.0
2727 (5 rows)
2729 select * from generate_series(0.1::numeric, 4.0::numeric, 1.3::numeric);
2730 generate_series
2731 -----------------
2732 0.1
2733 1.4
2734 2.7
2735 4.0
2736 (4 rows)
2738 select * from generate_series(4.0::numeric, -1.5::numeric, -2.2::numeric);
2739 generate_series
2740 -----------------
2741 4.0
2742 1.8
2743 -0.4
2744 (3 rows)
2746 -- Trigger errors
2747 select * from generate_series(-100::numeric, 100::numeric, 0::numeric);
2748 ERROR: step size cannot equal zero
2749 select * from generate_series(-100::numeric, 100::numeric, 'nan'::numeric);
2750 ERROR: step size cannot be NaN
2751 select * from generate_series('nan'::numeric, 100::numeric, 10::numeric);
2752 ERROR: start value cannot be NaN
2753 select * from generate_series(0::numeric, 'nan'::numeric, 10::numeric);
2754 ERROR: stop value cannot be NaN
2755 select * from generate_series('inf'::numeric, 'inf'::numeric, 10::numeric);
2756 ERROR: start value cannot be infinity
2757 select * from generate_series(0::numeric, 'inf'::numeric, 10::numeric);
2758 ERROR: stop value cannot be infinity
2759 select * from generate_series(0::numeric, '42'::numeric, '-inf'::numeric);
2760 ERROR: step size cannot be infinity
2761 -- Checks maximum, output is truncated
2762 select (i / (10::numeric ^ 131071))::numeric(1,0)
2763 from generate_series(6 * (10::numeric ^ 131071),
2764 9 * (10::numeric ^ 131071),
2765 10::numeric ^ 131071) as a(i);
2766 numeric
2767 ---------
2768 6
2769 7
2770 8
2771 9
2772 (4 rows)
2774 -- Check usage with variables
2775 select * from generate_series(1::numeric, 3::numeric) i, generate_series(i,3) j;
2776 i | j
2777 ---+---
2778 1 | 1
2779 1 | 2
2780 1 | 3
2781 2 | 2
2782 2 | 3
2783 3 | 3
2784 (6 rows)
2786 select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,i) j;
2787 i | j
2788 ---+---
2789 1 | 1
2790 2 | 1
2791 2 | 2
2792 3 | 1
2793 3 | 2
2794 3 | 3
2795 (6 rows)
2797 select * from generate_series(1::numeric, 3::numeric) i, generate_series(1,5,i) j;
2798 i | j
2799 ---+---
2800 1 | 1
2801 1 | 2
2802 1 | 3
2803 1 | 4
2804 1 | 5
2805 2 | 1
2806 2 | 3
2807 2 | 5
2808 3 | 1
2809 3 | 4
2810 (10 rows)
2812 --
2813 -- Tests for LN()
2814 --
2815 -- Invalid inputs
2816 select ln(-12.34);
2817 ERROR: cannot take logarithm of a negative number
2818 select ln(0.0);
2819 ERROR: cannot take logarithm of zero
2820 -- Some random tests
2821 select ln(1.2345678e-28);
2822 ln
2823 -----------------------------------------
2824 -64.26166165451762991204894255882820859
2825 (1 row)
2827 select ln(0.0456789);
2828 ln
2829 ---------------------
2830 -3.0861187944847439
2831 (1 row)
2833 select ln(0.349873948359354029493948309745709580730482050975);
2834 ln
2835 -----------------------------------------------------
2836 -1.050182336912082775693991697979750253056317885460
2837 (1 row)
2839 select ln(0.99949452);
2840 ln
2841 -------------------------
2842 -0.00050560779808326467
2843 (1 row)
2845 select ln(1.00049687395);
2846 ln
2847 ------------------------
2848 0.00049675054901370394
2849 (1 row)
2851 select ln(1234.567890123456789);
2852 ln
2853 --------------------
2854 7.1184763012977896
2855 (1 row)
2857 select ln(5.80397490724e5);
2858 ln
2859 --------------------
2860 13.271468476626518
2861 (1 row)
2863 select ln(9.342536355e34);
2864 ln
2865 --------------------
2866 80.522470935524187
2867 (1 row)
2869 --
2870 -- Tests for LOG() (base 10)
2871 --
2872 -- invalid inputs
2873 select log(-12.34);
2874 ERROR: cannot take logarithm of a negative number
2875 CONTEXT: SQL function "log" statement 1
2876 select log(0.0);
2877 ERROR: cannot take logarithm of zero
2878 CONTEXT: SQL function "log" statement 1
2879 -- some random tests
2880 select log(1.234567e-89);
2881 log
2882 -----------------------------------------------------------------------------------------------------
2883 -88.90848533591373725637496492944925187293052336306443143312825869985819779294142441287021741054275
2884 (1 row)
2886 select log(3.4634998359873254962349856073435545);
2887 log
2888 --------------------------------------
2889 0.5395151714070134409152404011959981
2890 (1 row)
2892 select log(9.999999999999999999);
2893 log
2894 ----------------------
2895 1.000000000000000000
2896 (1 row)
2898 select log(10.00000000000000000);
2899 log
2900 ---------------------
2901 1.00000000000000000
2902 (1 row)
2904 select log(10.00000000000000001);
2905 log
2906 ---------------------
2907 1.00000000000000000
2908 (1 row)
2910 select log(590489.45235237);
2911 log
2912 -------------------
2913 5.771212144411727
2914 (1 row)
2916 --
2917 -- Tests for LOG() (arbitrary base)
2918 --
2919 -- invalid inputs
2920 select log(-12.34, 56.78);
2921 ERROR: cannot take logarithm of a negative number
2922 select log(-12.34, -56.78);
2923 ERROR: cannot take logarithm of a negative number
2924 select log(12.34, -56.78);
2925 ERROR: cannot take logarithm of a negative number
2926 select log(0.0, 12.34);
2927 ERROR: cannot take logarithm of zero
2928 select log(12.34, 0.0);
2929 ERROR: cannot take logarithm of zero
2930 select log(1.0, 12.34);
2931 ERROR: division by zero
2932 -- some random tests
2933 select log(1.23e-89, 6.4689e45);
2934 log
2935 ------------------------------------------------------------------------------------------------
2936 -0.5152489207781856983977054971756484879653568168479201885425588841094788842469115325262329756
2937 (1 row)
2939 select log(0.99923, 4.58934e34);
2940 log
2941 ---------------------
2942 -103611.55579544132
2943 (1 row)
2945 select log(1.000016, 8.452010e18);
2946 log
2947 --------------------
2948 2723830.2877097365
2949 (1 row)
2951 select log(3.1954752e47, 9.4792021e-73);
2952 log
2953 -------------------------------------------------------------------------------------
2954 -1.51613372350688302142917386143459361608600157692779164475351842333265418126982165
2955 (1 row)
2957 --
2958 -- Tests for scale()
2959 --
2960 select scale(numeric 'NaN');
2961 scale
2962 -------
2964 (1 row)
2966 select scale(numeric 'inf');
2967 scale
2968 -------
2970 (1 row)
2972 select scale(NULL::numeric);
2973 scale
2974 -------
2976 (1 row)
2978 select scale(1.12);
2979 scale
2980 -------
2981 2
2982 (1 row)
2984 select scale(0);
2985 scale
2986 -------
2987 0
2988 (1 row)
2990 select scale(0.00);
2991 scale
2992 -------
2993 2
2994 (1 row)
2996 select scale(1.12345);
2997 scale
2998 -------
2999 5
3000 (1 row)
3002 select scale(110123.12475871856128);
3003 scale
3004 -------
3005 14
3006 (1 row)
3008 select scale(-1123.12471856128);
3009 scale
3010 -------
3011 11
3012 (1 row)
3014 select scale(-13.000000000000000);
3015 scale
3016 -------
3017 15
3018 (1 row)
3020 --
3021 -- Tests for min_scale()
3022 --
3023 select min_scale(numeric 'NaN') is NULL; -- should be true
3024 ?column?
3025 ----------
3026 t
3027 (1 row)
3029 select min_scale(numeric 'inf') is NULL; -- should be true
3030 ?column?
3031 ----------
3032 t
3033 (1 row)
3035 select min_scale(0); -- no digits
3036 min_scale
3037 -----------
3038 0
3039 (1 row)
3041 select min_scale(0.00); -- no digits again
3042 min_scale
3043 -----------
3044 0
3045 (1 row)
3047 select min_scale(1.0); -- no scale
3048 min_scale
3049 -----------
3050 0
3051 (1 row)
3053 select min_scale(1.1); -- scale 1
3054 min_scale
3055 -----------
3056 1
3057 (1 row)
3059 select min_scale(1.12); -- scale 2
3060 min_scale
3061 -----------
3062 2
3063 (1 row)
3065 select min_scale(1.123); -- scale 3
3066 min_scale
3067 -----------
3068 3
3069 (1 row)
3071 select min_scale(1.1234); -- scale 4, filled digit
3072 min_scale
3073 -----------
3074 4
3075 (1 row)
3077 select min_scale(1.12345); -- scale 5, 2 NDIGITS
3078 min_scale
3079 -----------
3080 5
3081 (1 row)
3083 select min_scale(1.1000); -- 1 pos in NDIGITS
3084 min_scale
3085 -----------
3086 1
3087 (1 row)
3089 select min_scale(1e100); -- very big number
3090 min_scale
3091 -----------
3092 0
3093 (1 row)
3095 --
3096 -- Tests for trim_scale()
3097 --
3098 select trim_scale(numeric 'NaN');
3099 trim_scale
3100 ------------
3101 NaN
3102 (1 row)
3104 select trim_scale(numeric 'inf');
3105 trim_scale
3106 ------------
3107 Infinity
3108 (1 row)
3110 select trim_scale(1.120);
3111 trim_scale
3112 ------------
3113 1.12
3114 (1 row)
3116 select trim_scale(0);
3117 trim_scale
3118 ------------
3119 0
3120 (1 row)
3122 select trim_scale(0.00);
3123 trim_scale
3124 ------------
3125 0
3126 (1 row)
3128 select trim_scale(1.1234500);
3129 trim_scale
3130 ------------
3131 1.12345
3132 (1 row)
3134 select trim_scale(110123.12475871856128000);
3135 trim_scale
3136 -----------------------
3137 110123.12475871856128
3138 (1 row)
3140 select trim_scale(-1123.124718561280000000);
3141 trim_scale
3142 -------------------
3143 -1123.12471856128
3144 (1 row)
3146 select trim_scale(-13.00000000000000000000);
3147 trim_scale
3148 ------------
3149 -13
3150 (1 row)
3152 select trim_scale(1e100);
3153 trim_scale
3154 -------------------------------------------------------------------------------------------------------
3155 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
3156 (1 row)
3158 --
3159 -- Tests for SUM()
3160 --
3161 -- cases that need carry propagation
3162 SELECT SUM(9999::numeric) FROM generate_series(1, 100000);
3163 sum
3164 -----------
3165 999900000
3166 (1 row)
3168 SELECT SUM((-9999)::numeric) FROM generate_series(1, 100000);
3169 sum
3170 ------------
3171 -999900000
3172 (1 row)
3174 --
3175 -- Tests for VARIANCE()
3176 --
3177 CREATE TABLE num_variance (a numeric);
3178 INSERT INTO num_variance VALUES (0);
3179 INSERT INTO num_variance VALUES (3e-500);
3180 INSERT INTO num_variance VALUES (-3e-500);
3181 INSERT INTO num_variance VALUES (4e-500 - 1e-16383);
3182 INSERT INTO num_variance VALUES (-4e-500 + 1e-16383);
3183 -- variance is just under 12.5e-1000 and so should round down to 12e-1000
3184 SELECT trim_scale(variance(a) * 1e1000) FROM num_variance;
3185 trim_scale
3186 ------------
3187 12
3188 (1 row)
3190 -- check that parallel execution produces the same result
3191 BEGIN;
3192 ALTER TABLE num_variance SET (parallel_workers = 4);
3193 SET LOCAL parallel_setup_cost = 0;
3194 SET LOCAL max_parallel_workers_per_gather = 4;
3195 SELECT trim_scale(variance(a) * 1e1000) FROM num_variance;
3196 trim_scale
3197 ------------
3198 12
3199 (1 row)
3201 ROLLBACK;
3202 -- case where sum of squares would overflow but variance does not
3203 DELETE FROM num_variance;
3204 INSERT INTO num_variance SELECT 9e131071 + x FROM generate_series(1, 5) x;
3205 SELECT variance(a) FROM num_variance;
3206 variance
3207 --------------------
3208 2.5000000000000000
3209 (1 row)
3211 -- check that parallel execution produces the same result
3212 BEGIN;
3213 ALTER TABLE num_variance SET (parallel_workers = 4);
3214 SET LOCAL parallel_setup_cost = 0;
3215 SET LOCAL max_parallel_workers_per_gather = 4;
3216 SELECT variance(a) FROM num_variance;
3217 variance
3218 --------------------
3219 2.5000000000000000
3220 (1 row)
3222 ROLLBACK;
3223 DROP TABLE num_variance;
3224 --
3225 -- Tests for GCD()
3226 --
3227 SELECT a, b, gcd(a, b), gcd(a, -b), gcd(-b, a), gcd(-b, -a)
3228 FROM (VALUES (0::numeric, 0::numeric),
3229 (0::numeric, numeric 'NaN'),
3230 (0::numeric, 46375::numeric),
3231 (433125::numeric, 46375::numeric),
3232 (43312.5::numeric, 4637.5::numeric),
3233 (4331.250::numeric, 463.75000::numeric),
3234 ('inf', '0'),
3235 ('inf', '42'),
3236 ('inf', 'inf')
3237 ) AS v(a, b);
3238 a | b | gcd | gcd | gcd | gcd
3239 ----------+-----------+---------+---------+---------+---------
3240 0 | 0 | 0 | 0 | 0 | 0
3241 0 | NaN | NaN | NaN | NaN | NaN
3242 0 | 46375 | 46375 | 46375 | 46375 | 46375
3243 433125 | 46375 | 875 | 875 | 875 | 875
3244 43312.5 | 4637.5 | 87.5 | 87.5 | 87.5 | 87.5
3245 4331.250 | 463.75000 | 8.75000 | 8.75000 | 8.75000 | 8.75000
3246 Infinity | 0 | NaN | NaN | NaN | NaN
3247 Infinity | 42 | NaN | NaN | NaN | NaN
3248 Infinity | Infinity | NaN | NaN | NaN | NaN
3249 (9 rows)
3251 --
3252 -- Tests for LCM()
3253 --
3254 SELECT a,b, lcm(a, b), lcm(a, -b), lcm(-b, a), lcm(-b, -a)
3255 FROM (VALUES (0::numeric, 0::numeric),
3256 (0::numeric, numeric 'NaN'),
3257 (0::numeric, 13272::numeric),
3258 (13272::numeric, 13272::numeric),
3259 (423282::numeric, 13272::numeric),
3260 (42328.2::numeric, 1327.2::numeric),
3261 (4232.820::numeric, 132.72000::numeric),
3262 ('inf', '0'),
3263 ('inf', '42'),
3264 ('inf', 'inf')
3265 ) AS v(a, b);
3266 a | b | lcm | lcm | lcm | lcm
3267 ----------+-----------+--------------+--------------+--------------+--------------
3268 0 | 0 | 0 | 0 | 0 | 0
3269 0 | NaN | NaN | NaN | NaN | NaN
3270 0 | 13272 | 0 | 0 | 0 | 0
3271 13272 | 13272 | 13272 | 13272 | 13272 | 13272
3272 423282 | 13272 | 11851896 | 11851896 | 11851896 | 11851896
3273 42328.2 | 1327.2 | 1185189.6 | 1185189.6 | 1185189.6 | 1185189.6
3274 4232.820 | 132.72000 | 118518.96000 | 118518.96000 | 118518.96000 | 118518.96000
3275 Infinity | 0 | NaN | NaN | NaN | NaN
3276 Infinity | 42 | NaN | NaN | NaN | NaN
3277 Infinity | Infinity | NaN | NaN | NaN | NaN
3278 (10 rows)
3280 SELECT lcm(9999 * (10::numeric)^131068 + (10::numeric^131068 - 1), 2); -- overflow
3281 ERROR: value overflows numeric format
3282 --
3283 -- Tests for factorial
3284 --
3285 SELECT factorial(4);
3286 factorial
3287 -----------
3288 24
3289 (1 row)
3291 SELECT factorial(15);
3292 factorial
3293 ---------------
3294 1307674368000
3295 (1 row)
3297 SELECT factorial(100000);
3298 ERROR: value overflows numeric format
3299 SELECT factorial(0);
3300 factorial
3301 -----------
3302 1
3303 (1 row)
3305 SELECT factorial(-4);
3306 ERROR: factorial of a negative number is undefined
3307 --
3308 -- Tests for pg_lsn()
3309 --
3310 SELECT pg_lsn(23783416::numeric);
3311 pg_lsn
3312 -----------
3313 0/16AE7F8
3314 (1 row)
3316 SELECT pg_lsn(0::numeric);
3317 pg_lsn
3318 --------
3319 0/0
3320 (1 row)
3322 SELECT pg_lsn(18446744073709551615::numeric);
3323 pg_lsn
3324 -------------------
3325 FFFFFFFF/FFFFFFFF
3326 (1 row)
3328 SELECT pg_lsn(-1::numeric);
3329 ERROR: pg_lsn out of range
3330 SELECT pg_lsn(18446744073709551616::numeric);
3331 ERROR: pg_lsn out of range
3332 SELECT pg_lsn('NaN'::numeric);
3333 ERROR: cannot convert NaN to pg_lsn