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