4 SELECT avg(four) AS avg_1 FROM onek;
10 SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
16 -- In 7.1, avg(float4) is computed using float8 arithmetic.
17 -- Round the result to 3 digits to avoid platform-specific results.
18 SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
24 SELECT avg(gpa) AS avg_3_4 FROM ONLY student;
30 SELECT sum(four) AS sum_1500 FROM onek;
36 SELECT sum(a) AS sum_198 FROM aggtest;
42 SELECT sum(b) AS avg_431_773 FROM aggtest;
48 SELECT sum(gpa) AS avg_6_8 FROM ONLY student;
54 SELECT max(four) AS max_3 FROM onek;
60 SELECT max(a) AS max_100 FROM aggtest;
66 SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
72 SELECT max(student.gpa) AS max_3_7 FROM student;
78 SELECT stddev_pop(b) FROM aggtest;
84 SELECT stddev_samp(b) FROM aggtest;
90 SELECT var_pop(b) FROM aggtest;
96 SELECT var_samp(b) FROM aggtest;
102 SELECT stddev_pop(b::numeric) FROM aggtest;
108 SELECT stddev_samp(b::numeric) FROM aggtest;
114 SELECT var_pop(b::numeric) FROM aggtest;
120 SELECT var_samp(b::numeric) FROM aggtest;
126 -- population variance is defined for a single tuple, sample variance
128 SELECT var_pop(1.0), var_samp(2.0);
134 SELECT stddev_pop(3.0::numeric), stddev_samp(4.0::numeric);
135 stddev_pop | stddev_samp
136 ------------+-------------
140 -- verify correct results for null and NaN inputs
141 select sum(null::int4) from generate_series(1,3);
147 select sum(null::int8) from generate_series(1,3);
153 select sum(null::numeric) from generate_series(1,3);
159 select sum(null::float8) from generate_series(1,3);
165 select avg(null::int4) from generate_series(1,3);
171 select avg(null::int8) from generate_series(1,3);
177 select avg(null::numeric) from generate_series(1,3);
183 select avg(null::float8) from generate_series(1,3);
189 select sum('NaN'::numeric) from generate_series(1,3);
195 select avg('NaN'::numeric) from generate_series(1,3);
201 -- SQL2003 binary aggregates
202 SELECT regr_count(b, a) FROM aggtest;
208 SELECT regr_sxx(b, a) FROM aggtest;
214 SELECT regr_syy(b, a) FROM aggtest;
220 SELECT regr_sxy(b, a) FROM aggtest;
226 SELECT regr_avgx(b, a), regr_avgy(b, a) FROM aggtest;
227 regr_avgx | regr_avgy
228 -----------+------------------
229 49.5 | 107.943152273074
232 SELECT regr_r2(b, a) FROM aggtest;
238 SELECT regr_slope(b, a), regr_intercept(b, a) FROM aggtest;
239 regr_slope | regr_intercept
240 -------------------+------------------
241 0.512750700441271 | 82.5619926012309
244 SELECT covar_pop(b, a), covar_samp(b, a) FROM aggtest;
245 covar_pop | covar_samp
246 -----------------+------------------
247 653.62895538751 | 871.505273850014
250 SELECT corr(b, a) FROM aggtest;
256 SELECT count(four) AS cnt_1000 FROM onek;
262 SELECT count(DISTINCT four) AS cnt_4 FROM onek;
268 select ten, count(*), sum(four) from onek
269 group by ten order by ten;
284 select ten, count(four), sum(DISTINCT four) from onek
285 group by ten order by ten;
300 -- user-defined aggregates
301 SELECT newavg(four) AS avg_1 FROM onek;
307 SELECT newsum(four) AS sum_1500 FROM onek;
313 SELECT newcnt(four) AS cnt_1000 FROM onek;
319 SELECT newcnt(*) AS cnt_1000 FROM onek;
325 SELECT oldcnt(*) AS cnt_1000 FROM onek;
331 SELECT sum2(q1,q2) FROM int8_tbl;
337 -- test for outer-level aggregates
339 select ten, sum(distinct four) from onek a
341 having exists (select 1 from onek b where sum(distinct a.four) = b.four);
351 -- this should fail because subquery has an agg of its own in WHERE
352 select ten, sum(distinct four) from onek a
354 having exists (select 1 from onek b
355 where sum(distinct a.four + b.four) = b.four);
356 ERROR: aggregate functions are not allowed in WHERE
357 LINE 4: where sum(distinct a.four + b.four) = b.four)...
359 -- Test handling of sublinks within outer-level aggregates.
360 -- Per bug report from Daniel Grace.
362 (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
370 -- test for bitwise integer aggregates
372 CREATE TEMPORARY TABLE bitwise_test(
390 COPY bitwise_test FROM STDIN NULL 'null';
397 BIT_AND(y) AS "0100",
405 1 | 1 | 1 | ? | 0 | 0100 | 7 | 7 | 7 | ? | 7 | 1101
406 ---+---+---+---+---+------+---+---+---+---+---+------
407 1 | 1 | 1 | 1 | 0 | 0100 | 7 | 7 | 7 | 3 | 7 | 1101
411 -- test boolean aggregates
413 -- first test all possible transition and final states
415 -- boolean and transitions
416 -- null because strict
417 booland_statefunc(NULL, NULL) IS NULL AS "t",
418 booland_statefunc(TRUE, NULL) IS NULL AS "t",
419 booland_statefunc(FALSE, NULL) IS NULL AS "t",
420 booland_statefunc(NULL, TRUE) IS NULL AS "t",
421 booland_statefunc(NULL, FALSE) IS NULL AS "t",
422 -- and actual computations
423 booland_statefunc(TRUE, TRUE) AS "t",
424 NOT booland_statefunc(TRUE, FALSE) AS "t",
425 NOT booland_statefunc(FALSE, TRUE) AS "t",
426 NOT booland_statefunc(FALSE, FALSE) AS "t";
427 t | t | t | t | t | t | t | t | t
428 ---+---+---+---+---+---+---+---+---
429 t | t | t | t | t | t | t | t | t
433 -- boolean or transitions
434 -- null because strict
435 boolor_statefunc(NULL, NULL) IS NULL AS "t",
436 boolor_statefunc(TRUE, NULL) IS NULL AS "t",
437 boolor_statefunc(FALSE, NULL) IS NULL AS "t",
438 boolor_statefunc(NULL, TRUE) IS NULL AS "t",
439 boolor_statefunc(NULL, FALSE) IS NULL AS "t",
440 -- actual computations
441 boolor_statefunc(TRUE, TRUE) AS "t",
442 boolor_statefunc(TRUE, FALSE) AS "t",
443 boolor_statefunc(FALSE, TRUE) AS "t",
444 NOT boolor_statefunc(FALSE, FALSE) AS "t";
445 t | t | t | t | t | t | t | t | t
446 ---+---+---+---+---+---+---+---+---
447 t | t | t | t | t | t | t | t | t
450 CREATE TEMPORARY TABLE bool_test(
465 COPY bool_test FROM STDIN NULL 'null';
471 BOOL_AND(NOT b2) AS "f",
472 BOOL_AND(NOT b3) AS "t"
474 f | t | f | n | f | t
475 ---+---+---+---+---+---
484 EVERY(NOT b2) AS "f",
487 f | t | f | n | f | t
488 ---+---+---+---+---+---
497 BOOL_OR(NOT b2) AS "f",
498 BOOL_OR(NOT b3) AS "t"
500 t | t | f | n | f | t
501 ---+---+---+---+---+---
506 -- Test cases that should be optimized into indexscans instead of
507 -- the generic aggregate implementation.
511 select min(unique1) from tenk1;
513 ------------------------------------------------------------
515 InitPlan 1 (returns $0)
517 -> Index Only Scan using tenk1_unique1 on tenk1
518 Index Cond: (unique1 IS NOT NULL)
521 select min(unique1) from tenk1;
528 select max(unique1) from tenk1;
530 ---------------------------------------------------------------------
532 InitPlan 1 (returns $0)
534 -> Index Only Scan Backward using tenk1_unique1 on tenk1
535 Index Cond: (unique1 IS NOT NULL)
538 select max(unique1) from tenk1;
545 select max(unique1) from tenk1 where unique1 < 42;
547 ------------------------------------------------------------------------
549 InitPlan 1 (returns $0)
551 -> Index Only Scan Backward using tenk1_unique1 on tenk1
552 Index Cond: ((unique1 IS NOT NULL) AND (unique1 < 42))
555 select max(unique1) from tenk1 where unique1 < 42;
562 select max(unique1) from tenk1 where unique1 > 42;
564 ------------------------------------------------------------------------
566 InitPlan 1 (returns $0)
568 -> Index Only Scan Backward using tenk1_unique1 on tenk1
569 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42))
572 select max(unique1) from tenk1 where unique1 > 42;
579 select max(unique1) from tenk1 where unique1 > 42000;
581 ---------------------------------------------------------------------------
583 InitPlan 1 (returns $0)
585 -> Index Only Scan Backward using tenk1_unique1 on tenk1
586 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000))
589 select max(unique1) from tenk1 where unique1 > 42000;
595 -- multi-column index (uses tenk1_thous_tenthous)
597 select max(tenthous) from tenk1 where thousand = 33;
599 ----------------------------------------------------------------------------
601 InitPlan 1 (returns $0)
603 -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1
604 Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL))
607 select max(tenthous) from tenk1 where thousand = 33;
614 select min(tenthous) from tenk1 where thousand = 33;
616 --------------------------------------------------------------------------
618 InitPlan 1 (returns $0)
620 -> Index Only Scan using tenk1_thous_tenthous on tenk1
621 Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL))
624 select min(tenthous) from tenk1 where thousand = 33;
630 -- check parameter propagation into an indexscan subquery
632 select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
635 -----------------------------------------------------------------------------------------
639 InitPlan 1 (returns $1)
641 -> Index Only Scan using tenk1_unique1 on tenk1
642 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > int4_tbl.f1))
645 select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
656 -- check some cases that were handled incorrectly in 8.3.0
658 select distinct max(unique2) from tenk1;
660 ---------------------------------------------------------------------
663 InitPlan 1 (returns $0)
665 -> Index Only Scan Backward using tenk1_unique2 on tenk1
666 Index Cond: (unique2 IS NOT NULL)
670 select distinct max(unique2) from tenk1;
677 select max(unique2) from tenk1 order by 1;
679 ---------------------------------------------------------------------
682 InitPlan 1 (returns $0)
684 -> Index Only Scan Backward using tenk1_unique2 on tenk1
685 Index Cond: (unique2 IS NOT NULL)
689 select max(unique2) from tenk1 order by 1;
696 select max(unique2) from tenk1 order by max(unique2);
698 ---------------------------------------------------------------------
701 InitPlan 1 (returns $0)
703 -> Index Only Scan Backward using tenk1_unique2 on tenk1
704 Index Cond: (unique2 IS NOT NULL)
708 select max(unique2) from tenk1 order by max(unique2);
715 select max(unique2) from tenk1 order by max(unique2)+1;
717 ---------------------------------------------------------------------
720 InitPlan 1 (returns $0)
722 -> Index Only Scan Backward using tenk1_unique2 on tenk1
723 Index Cond: (unique2 IS NOT NULL)
727 select max(unique2) from tenk1 order by max(unique2)+1;
734 select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
736 ---------------------------------------------------------------------
738 Sort Key: (generate_series(1, 3))
739 InitPlan 1 (returns $0)
741 -> Index Only Scan Backward using tenk1_unique2 on tenk1
742 Index Cond: (unique2 IS NOT NULL)
746 select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
754 -- try it on an inheritance tree
755 create table minmaxtest(f1 int);
756 create table minmaxtest1() inherits (minmaxtest);
757 create table minmaxtest2() inherits (minmaxtest);
758 create table minmaxtest3() inherits (minmaxtest);
759 create index minmaxtesti on minmaxtest(f1);
760 create index minmaxtest1i on minmaxtest1(f1);
761 create index minmaxtest2i on minmaxtest2(f1 desc);
762 create index minmaxtest3i on minmaxtest3(f1) where f1 is not null;
763 insert into minmaxtest values(11), (12);
764 insert into minmaxtest1 values(13), (14);
765 insert into minmaxtest2 values(15), (16);
766 insert into minmaxtest3 values(17), (18);
768 select min(f1), max(f1) from minmaxtest;
770 ----------------------------------------------------------------------------------------------
772 InitPlan 1 (returns $0)
775 Sort Key: minmaxtest.f1
776 -> Index Only Scan using minmaxtesti on minmaxtest
777 Index Cond: (f1 IS NOT NULL)
778 -> Index Only Scan using minmaxtest1i on minmaxtest1
779 Index Cond: (f1 IS NOT NULL)
780 -> Index Only Scan Backward using minmaxtest2i on minmaxtest2
781 Index Cond: (f1 IS NOT NULL)
782 -> Index Only Scan using minmaxtest3i on minmaxtest3
783 Index Cond: (f1 IS NOT NULL)
784 InitPlan 2 (returns $1)
787 Sort Key: minmaxtest_1.f1
788 -> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1
789 Index Cond: (f1 IS NOT NULL)
790 -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1
791 Index Cond: (f1 IS NOT NULL)
792 -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest2_1
793 Index Cond: (f1 IS NOT NULL)
794 -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1
795 Index Cond: (f1 IS NOT NULL)
798 select min(f1), max(f1) from minmaxtest;
804 -- DISTINCT doesn't do anything useful here, but it shouldn't fail
806 select distinct min(f1), max(f1) from minmaxtest;
808 ----------------------------------------------------------------------------------------------
811 InitPlan 1 (returns $0)
814 Sort Key: minmaxtest.f1
815 -> Index Only Scan using minmaxtesti on minmaxtest
816 Index Cond: (f1 IS NOT NULL)
817 -> Index Only Scan using minmaxtest1i on minmaxtest1
818 Index Cond: (f1 IS NOT NULL)
819 -> Index Only Scan Backward using minmaxtest2i on minmaxtest2
820 Index Cond: (f1 IS NOT NULL)
821 -> Index Only Scan using minmaxtest3i on minmaxtest3
822 Index Cond: (f1 IS NOT NULL)
823 InitPlan 2 (returns $1)
826 Sort Key: minmaxtest_1.f1
827 -> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1
828 Index Cond: (f1 IS NOT NULL)
829 -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1
830 Index Cond: (f1 IS NOT NULL)
831 -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest2_1
832 Index Cond: (f1 IS NOT NULL)
833 -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1
834 Index Cond: (f1 IS NOT NULL)
838 select distinct min(f1), max(f1) from minmaxtest;
844 drop table minmaxtest cascade;
845 NOTICE: drop cascades to 3 other objects
846 DETAIL: drop cascades to table minmaxtest1
847 drop cascades to table minmaxtest2
848 drop cascades to table minmaxtest3
849 -- check for correct detection of nested-aggregate errors
850 select max(min(unique1)) from tenk1;
851 ERROR: aggregate function calls cannot be nested
852 LINE 1: select max(min(unique1)) from tenk1;
854 select (select max(min(unique1)) from int8_tbl) from tenk1;
855 ERROR: aggregate function calls cannot be nested
856 LINE 1: select (select max(min(unique1)) from int8_tbl) from tenk1;
859 -- Test combinations of DISTINCT and/or ORDER BY
861 select array_agg(a order by b)
862 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
868 select array_agg(a order by a)
869 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
875 select array_agg(a order by a desc)
876 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
882 select array_agg(b order by a desc)
883 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
889 select array_agg(distinct a)
890 from (values (1),(2),(1),(3),(null),(2)) v(a);
896 select array_agg(distinct a order by a)
897 from (values (1),(2),(1),(3),(null),(2)) v(a);
903 select array_agg(distinct a order by a desc)
904 from (values (1),(2),(1),(3),(null),(2)) v(a);
910 select array_agg(distinct a order by a desc nulls last)
911 from (values (1),(2),(1),(3),(null),(2)) v(a);
917 -- multi-arg aggs, strict/nonstrict, distinct/order by
918 select aggfstr(a,b,c)
919 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
921 ---------------------------------------
922 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
926 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
928 -----------------------------------------------
929 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
932 select aggfstr(distinct a,b,c)
933 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
934 generate_series(1,3) i;
936 ---------------------------------------
937 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
940 select aggfns(distinct a,b,c)
941 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
942 generate_series(1,3) i;
944 -----------------------------------------------
945 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
948 select aggfstr(distinct a,b,c order by b)
949 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
950 generate_series(1,3) i;
952 ---------------------------------------
953 {"(3,1,baz)","(2,2,bar)","(1,3,foo)"}
956 select aggfns(distinct a,b,c order by b)
957 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
958 generate_series(1,3) i;
960 -----------------------------------------------
961 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
964 -- test specific code paths
965 select aggfns(distinct a,a,c order by c using ~<~,a)
966 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
967 generate_series(1,2) i;
969 ------------------------------------------------
970 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
973 select aggfns(distinct a,a,c order by c using ~<~)
974 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
975 generate_series(1,2) i;
977 ------------------------------------------------
978 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
981 select aggfns(distinct a,a,c order by a)
982 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
983 generate_series(1,2) i;
985 ------------------------------------------------
986 {"(0,0,)","(1,1,foo)","(2,2,bar)","(3,3,baz)"}
989 select aggfns(distinct a,b,c order by a,c using ~<~,b)
990 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
991 generate_series(1,2) i;
993 -----------------------------------------------
994 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
997 -- check node I/O via view creation and usage, also deparsing logic
998 create view agg_view1 as
1000 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
1001 select * from agg_view1;
1003 -----------------------------------------------
1004 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
1007 select pg_get_viewdef('agg_view1'::regclass);
1009 ---------------------------------------------------------------------------------------------------------------------
1010 SELECT aggfns(v.a, v.b, v.c) AS aggfns +
1011 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
1014 create or replace view agg_view1 as
1015 select aggfns(distinct a,b,c)
1016 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
1017 generate_series(1,3) i;
1018 select * from agg_view1;
1020 -----------------------------------------------
1021 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
1024 select pg_get_viewdef('agg_view1'::regclass);
1026 ---------------------------------------------------------------------------------------------------------------------
1027 SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns +
1028 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+
1029 generate_series(1, 3) i(i);
1032 create or replace view agg_view1 as
1033 select aggfns(distinct a,b,c order by b)
1034 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
1035 generate_series(1,3) i;
1036 select * from agg_view1;
1038 -----------------------------------------------
1039 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
1042 select pg_get_viewdef('agg_view1'::regclass);
1044 ---------------------------------------------------------------------------------------------------------------------
1045 SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns +
1046 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+
1047 generate_series(1, 3) i(i);
1050 create or replace view agg_view1 as
1051 select aggfns(a,b,c order by b+1)
1052 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
1053 select * from agg_view1;
1055 -----------------------------------------------
1056 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
1059 select pg_get_viewdef('agg_view1'::regclass);
1061 ---------------------------------------------------------------------------------------------------------------------
1062 SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns +
1063 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
1066 create or replace view agg_view1 as
1067 select aggfns(a,a,c order by b)
1068 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
1069 select * from agg_view1;
1071 ------------------------------------------------
1072 {"(3,3,baz)","(2,2,bar)","(1,1,foo)","(0,0,)"}
1075 select pg_get_viewdef('agg_view1'::regclass);
1077 ---------------------------------------------------------------------------------------------------------------------
1078 SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns +
1079 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
1082 create or replace view agg_view1 as
1083 select aggfns(a,b,c order by c using ~<~)
1084 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
1085 select * from agg_view1;
1087 -----------------------------------------------
1088 {"(2,2,bar)","(3,1,baz)","(1,3,foo)","(0,,)"}
1091 select pg_get_viewdef('agg_view1'::regclass);
1093 ---------------------------------------------------------------------------------------------------------------------
1094 SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns +
1095 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
1098 create or replace view agg_view1 as
1099 select aggfns(distinct a,b,c order by a,c using ~<~,b)
1100 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
1101 generate_series(1,2) i;
1102 select * from agg_view1;
1104 -----------------------------------------------
1105 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
1108 select pg_get_viewdef('agg_view1'::regclass);
1110 ---------------------------------------------------------------------------------------------------------------------
1111 SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns +
1112 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+
1113 generate_series(1, 2) i(i);
1116 drop view agg_view1;
1117 -- incorrect DISTINCT usage errors
1118 select aggfns(distinct a,b,c order by i)
1119 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1120 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1121 LINE 1: select aggfns(distinct a,b,c order by i)
1123 select aggfns(distinct a,b,c order by a,b+1)
1124 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1125 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1126 LINE 1: select aggfns(distinct a,b,c order by a,b+1)
1128 select aggfns(distinct a,b,c order by a,b,i,c)
1129 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1130 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1131 LINE 1: select aggfns(distinct a,b,c order by a,b,i,c)
1133 select aggfns(distinct a,a,c order by a,b)
1134 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1135 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1136 LINE 1: select aggfns(distinct a,a,c order by a,b)
1139 select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
1145 select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
1151 select string_agg(a,'AB') from (values(null),(null),('bbbb'),('cccc')) g(a);
1157 select string_agg(a,',') from (values(null),(null)) g(a);
1163 -- check some implicit casting cases, as per bug #5564
1164 select string_agg(distinct f1, ',' order by f1) from varchar_tbl; -- ok
1170 select string_agg(distinct f1::text, ',' order by f1) from varchar_tbl; -- not ok
1171 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1172 LINE 1: select string_agg(distinct f1::text, ',' order by f1) from v...
1174 select string_agg(distinct f1, ',' order by f1::text) from varchar_tbl; -- not ok
1175 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1176 LINE 1: select string_agg(distinct f1, ',' order by f1::text) from v...
1178 select string_agg(distinct f1::text, ',' order by f1::text) from varchar_tbl; -- ok
1184 -- string_agg bytea tests
1185 create table bytea_test_table(v bytea);
1186 select string_agg(v, '') from bytea_test_table;
1192 insert into bytea_test_table values(decode('ff','hex'));
1193 select string_agg(v, '') from bytea_test_table;
1199 insert into bytea_test_table values(decode('aa','hex'));
1200 select string_agg(v, '') from bytea_test_table;
1206 select string_agg(v, NULL) from bytea_test_table;
1212 select string_agg(v, decode('ee', 'hex')) from bytea_test_table;
1218 drop table bytea_test_table;
1220 select min(unique1) filter (where unique1 > 100) from tenk1;
1226 select ten, sum(distinct four) filter (where four::text ~ '123') from onek a
1242 select ten, sum(distinct four) filter (where four > 10) from onek a
1244 having exists (select 1 from onek b where sum(distinct a.four) = b.four);
1254 select max(foo COLLATE "C") filter (where (bar collate "POSIX") > '0')
1255 from (values ('a', 'b')) AS v(foo,bar);
1261 -- outer reference in FILTER (PostgreSQL extension)
1262 select (select count(*)
1263 from (values (1)) t0(inner_c))
1264 from (values (2),(3)) t1(outer_c); -- inner query is aggregation query
1271 select (select count(*) filter (where outer_c <> 0)
1272 from (values (1)) t0(inner_c))
1273 from (values (2),(3)) t1(outer_c); -- outer query is aggregation query
1279 select (select count(inner_c) filter (where outer_c <> 0)
1280 from (values (1)) t0(inner_c))
1281 from (values (2),(3)) t1(outer_c); -- inner query is aggregation query
1289 (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1))
1290 filter (where o.unique1 < 10))
1291 from tenk1 o; -- outer query is aggregation query
1297 -- subquery in FILTER clause (PostgreSQL extension)
1298 select sum(unique1) FILTER (WHERE
1299 unique1 IN (SELECT unique1 FROM onek where unique1 < 100)) FROM tenk1;
1305 -- exercise lots of aggregate parts with FILTER
1306 select aggfns(distinct a,b,c order by a,c using ~<~,b) filter (where a > 1)
1307 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
1308 generate_series(1,2) i;
1310 ---------------------------
1311 {"(2,2,bar)","(3,1,baz)"}
1314 -- variadic aggregates
1315 select least_agg(q1,q2) from int8_tbl;
1321 select least_agg(variadic array[q1,q2]) from int8_tbl;