5 SELECT avg(four) AS avg_1 FROM onek;
7 SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
9 -- In 7.1, avg(float4) is computed using float8 arithmetic.
10 -- Round the result to 3 digits to avoid platform-specific results.
12 SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
14 SELECT avg(gpa) AS avg_3_4 FROM ONLY student;
17 SELECT sum(four) AS sum_1500 FROM onek;
18 SELECT sum(a) AS sum_198 FROM aggtest;
19 SELECT sum(b) AS avg_431_773 FROM aggtest;
20 SELECT sum(gpa) AS avg_6_8 FROM ONLY student;
22 SELECT max(four) AS max_3 FROM onek;
23 SELECT max(a) AS max_100 FROM aggtest;
24 SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
25 SELECT max(student.gpa) AS max_3_7 FROM student;
27 SELECT stddev_pop(b) FROM aggtest;
28 SELECT stddev_samp(b) FROM aggtest;
29 SELECT var_pop(b) FROM aggtest;
30 SELECT var_samp(b) FROM aggtest;
32 SELECT stddev_pop(b::numeric) FROM aggtest;
33 SELECT stddev_samp(b::numeric) FROM aggtest;
34 SELECT var_pop(b::numeric) FROM aggtest;
35 SELECT var_samp(b::numeric) FROM aggtest;
37 -- population variance is defined for a single tuple, sample variance
39 SELECT var_pop(1.0), var_samp(2.0);
40 SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
42 -- SQL2003 binary aggregates
43 SELECT regr_count(b, a) FROM aggtest;
44 SELECT regr_sxx(b, a) FROM aggtest;
45 SELECT regr_syy(b, a) FROM aggtest;
46 SELECT regr_sxy(b, a) FROM aggtest;
47 SELECT regr_avgx(b, a), regr_avgy(b, a) FROM aggtest;
48 SELECT regr_r2(b, a) FROM aggtest;
49 SELECT regr_slope(b, a), regr_intercept(b, a) FROM aggtest;
50 SELECT covar_pop(b, a), covar_samp(b, a) FROM aggtest;
51 SELECT corr(b, a) FROM aggtest;
53 SELECT count(four) AS cnt_1000 FROM onek;
54 SELECT count(DISTINCT four) AS cnt_4 FROM onek;
56 select ten, count(*), sum(four) from onek
57 group by ten order by ten;
59 select ten, count(four), sum(DISTINCT four) from onek
60 group by ten order by ten;
62 -- user-defined aggregates
63 SELECT newavg(four) AS avg_1 FROM onek;
64 SELECT newsum(four) AS sum_1500 FROM onek;
65 SELECT newcnt(four) AS cnt_1000 FROM onek;
66 SELECT newcnt(*) AS cnt_1000 FROM onek;
67 SELECT oldcnt(*) AS cnt_1000 FROM onek;
68 SELECT sum2(q1,q2) FROM int8_tbl;
70 -- test for outer-level aggregates
73 select ten, sum(distinct four) from onek a
75 having exists (select 1 from onek b where sum(distinct a.four) = b.four);
77 -- this should fail because subquery has an agg of its own in WHERE
78 select ten, sum(distinct four) from onek a
80 having exists (select 1 from onek b
81 where sum(distinct a.four + b.four) = b.four);
83 -- Test handling of sublinks within outer-level aggregates.
84 -- Per bug report from Daniel Grace.
86 (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
90 -- test for bitwise integer aggregates
92 CREATE TEMPORARY TABLE bitwise_test(
107 COPY bitwise_test FROM STDIN NULL 'null';
119 BIT_AND(y) AS "0100",
130 -- test boolean aggregates
132 -- first test all possible transition and final states
135 -- boolean and transitions
136 -- null because strict
137 booland_statefunc(NULL, NULL) IS NULL AS "t",
138 booland_statefunc(TRUE, NULL) IS NULL AS "t",
139 booland_statefunc(FALSE, NULL) IS NULL AS "t",
140 booland_statefunc(NULL, TRUE) IS NULL AS "t",
141 booland_statefunc(NULL, FALSE) IS NULL AS "t",
142 -- and actual computations
143 booland_statefunc(TRUE, TRUE) AS "t",
144 NOT booland_statefunc(TRUE, FALSE) AS "t",
145 NOT booland_statefunc(FALSE, TRUE) AS "t",
146 NOT booland_statefunc(FALSE, FALSE) AS "t";
149 -- boolean or transitions
150 -- null because strict
151 boolor_statefunc(NULL, NULL) IS NULL AS "t",
152 boolor_statefunc(TRUE, NULL) IS NULL AS "t",
153 boolor_statefunc(FALSE, NULL) IS NULL AS "t",
154 boolor_statefunc(NULL, TRUE) IS NULL AS "t",
155 boolor_statefunc(NULL, FALSE) IS NULL AS "t",
156 -- actual computations
157 boolor_statefunc(TRUE, TRUE) AS "t",
158 boolor_statefunc(TRUE, FALSE) AS "t",
159 boolor_statefunc(FALSE, TRUE) AS "t",
160 NOT boolor_statefunc(FALSE, FALSE) AS "t";
162 CREATE TEMPORARY TABLE bool_test(
174 COPY bool_test FROM STDIN NULL 'null';
185 BOOL_AND(NOT b2) AS "f",
186 BOOL_AND(NOT b3) AS "t"
194 EVERY(NOT b2) AS "f",
203 BOOL_OR(NOT b2) AS "f",
204 BOOL_OR(NOT b3) AS "t"
208 -- Test several cases that should be optimized into indexscans instead of
209 -- the generic aggregate implementation. We can't actually verify that they
210 -- are done as indexscans, but we can check that the results are correct.
214 select max(unique1) from tenk1;
215 select max(unique1) from tenk1 where unique1 < 42;
216 select max(unique1) from tenk1 where unique1 > 42;
217 select max(unique1) from tenk1 where unique1 > 42000;
219 -- multi-column index (uses tenk1_thous_tenthous)
220 select max(tenthous) from tenk1 where thousand = 33;
221 select min(tenthous) from tenk1 where thousand = 33;
223 -- check parameter propagation into an indexscan subquery
224 select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
227 -- check some cases that were handled incorrectly in 8.3.0
228 select distinct max(unique2) from tenk1;
229 select max(unique2) from tenk1 order by 1;
230 select max(unique2) from tenk1 order by max(unique2);
231 select max(unique2) from tenk1 order by max(unique2)+1;
232 select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
235 -- Test combinations of DISTINCT and/or ORDER BY
238 select array_agg(a order by b)
239 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
240 select array_agg(a order by a)
241 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
242 select array_agg(a order by a desc)
243 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
244 select array_agg(b order by a desc)
245 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
247 select array_agg(distinct a)
248 from (values (1),(2),(1),(3),(null),(2)) v(a);
249 select array_agg(distinct a order by a)
250 from (values (1),(2),(1),(3),(null),(2)) v(a);
251 select array_agg(distinct a order by a desc)
252 from (values (1),(2),(1),(3),(null),(2)) v(a);
253 select array_agg(distinct a order by a desc nulls last)
254 from (values (1),(2),(1),(3),(null),(2)) v(a);
256 -- multi-arg aggs, strict/nonstrict, distinct/order by
258 select aggfstr(a,b,c)
259 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
261 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
263 select aggfstr(distinct a,b,c)
264 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
265 generate_series(1,3) i;
266 select aggfns(distinct a,b,c)
267 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
268 generate_series(1,3) i;
270 select aggfstr(distinct a,b,c order by b)
271 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
272 generate_series(1,3) i;
273 select aggfns(distinct a,b,c order by b)
274 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
275 generate_series(1,3) i;
277 -- test specific code paths
279 select aggfns(distinct a,a,c order by c using ~<~,a)
280 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
281 generate_series(1,2) i;
282 select aggfns(distinct a,a,c order by c using ~<~)
283 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
284 generate_series(1,2) i;
285 select aggfns(distinct a,a,c order by a)
286 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
287 generate_series(1,2) i;
288 select aggfns(distinct a,b,c order by a,c using ~<~,b)
289 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
290 generate_series(1,2) i;
292 -- check node I/O via view creation and usage, also deparsing logic
294 create view agg_view1 as
296 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
298 select * from agg_view1;
299 select pg_get_viewdef('agg_view1'::regclass);
301 create or replace view agg_view1 as
302 select aggfns(distinct a,b,c)
303 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
304 generate_series(1,3) i;
306 select * from agg_view1;
307 select pg_get_viewdef('agg_view1'::regclass);
309 create or replace view agg_view1 as
310 select aggfns(distinct a,b,c order by b)
311 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
312 generate_series(1,3) i;
314 select * from agg_view1;
315 select pg_get_viewdef('agg_view1'::regclass);
317 create or replace view agg_view1 as
318 select aggfns(a,b,c order by b+1)
319 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
321 select * from agg_view1;
322 select pg_get_viewdef('agg_view1'::regclass);
324 create or replace view agg_view1 as
325 select aggfns(a,a,c order by b)
326 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
328 select * from agg_view1;
329 select pg_get_viewdef('agg_view1'::regclass);
331 create or replace view agg_view1 as
332 select aggfns(a,b,c order by c using ~<~)
333 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
335 select * from agg_view1;
336 select pg_get_viewdef('agg_view1'::regclass);
338 create or replace view agg_view1 as
339 select aggfns(distinct a,b,c order by a,c using ~<~,b)
340 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
341 generate_series(1,2) i;
343 select * from agg_view1;
344 select pg_get_viewdef('agg_view1'::regclass);
348 -- incorrect DISTINCT usage errors
350 select aggfns(distinct a,b,c order by i)
351 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
352 select aggfns(distinct a,b,c order by a,b+1)
353 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
354 select aggfns(distinct a,b,c order by a,b,i,c)
355 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
356 select aggfns(distinct a,a,c order by a,b)
357 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
360 select string_agg(a) from (values('aaaa'),('bbbb'),('cccc')) g(a);
361 select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
362 select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
363 select string_agg(a,',') from (values(null),(null),('bbbb'),('cccc')) g(a);
364 select string_agg(a,',') from (values(null),(null)) g(a);
366 -- check some implicit casting cases, as per bug #5564
367 select string_agg(distinct f1 order by f1) from varchar_tbl; -- ok
368 select string_agg(distinct f1::text order by f1) from varchar_tbl; -- not ok
369 select string_agg(distinct f1 order by f1::text) from varchar_tbl; -- not ok
370 select string_agg(distinct f1::text order by f1::text) from varchar_tbl; -- ok