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 ---------------------------------------------------------------------
662 InitPlan 1 (returns $0)
664 -> Index Only Scan Backward using tenk1_unique2 on tenk1
665 Index Cond: (unique2 IS NOT NULL)
669 select distinct max(unique2) from tenk1;
676 select max(unique2) from tenk1 order by 1;
678 ---------------------------------------------------------------------
681 InitPlan 1 (returns $0)
683 -> Index Only Scan Backward using tenk1_unique2 on tenk1
684 Index Cond: (unique2 IS NOT NULL)
688 select max(unique2) from tenk1 order by 1;
695 select max(unique2) from tenk1 order by max(unique2);
697 ---------------------------------------------------------------------
700 InitPlan 1 (returns $0)
702 -> Index Only Scan Backward using tenk1_unique2 on tenk1
703 Index Cond: (unique2 IS NOT NULL)
707 select max(unique2) from tenk1 order by max(unique2);
714 select max(unique2) from tenk1 order by max(unique2)+1;
716 ---------------------------------------------------------------------
719 InitPlan 1 (returns $0)
721 -> Index Only Scan Backward using tenk1_unique2 on tenk1
722 Index Cond: (unique2 IS NOT NULL)
726 select max(unique2) from tenk1 order by max(unique2)+1;
733 select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
735 ---------------------------------------------------------------------
737 Sort Key: (generate_series(1, 3))
738 InitPlan 1 (returns $0)
740 -> Index Only Scan Backward using tenk1_unique2 on tenk1
741 Index Cond: (unique2 IS NOT NULL)
745 select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
753 -- try it on an inheritance tree
754 create table minmaxtest(f1 int);
755 create table minmaxtest1() inherits (minmaxtest);
756 create table minmaxtest2() inherits (minmaxtest);
757 create table minmaxtest3() inherits (minmaxtest);
758 create index minmaxtesti on minmaxtest(f1);
759 create index minmaxtest1i on minmaxtest1(f1);
760 create index minmaxtest2i on minmaxtest2(f1 desc);
761 create index minmaxtest3i on minmaxtest3(f1) where f1 is not null;
762 insert into minmaxtest values(11), (12);
763 insert into minmaxtest1 values(13), (14);
764 insert into minmaxtest2 values(15), (16);
765 insert into minmaxtest3 values(17), (18);
767 select min(f1), max(f1) from minmaxtest;
769 ----------------------------------------------------------------------------------------------
771 InitPlan 1 (returns $0)
774 Sort Key: minmaxtest.f1
775 -> Index Only Scan using minmaxtesti on minmaxtest
776 Index Cond: (f1 IS NOT NULL)
777 -> Index Only Scan using minmaxtest1i on minmaxtest1
778 Index Cond: (f1 IS NOT NULL)
779 -> Index Only Scan Backward using minmaxtest2i on minmaxtest2
780 Index Cond: (f1 IS NOT NULL)
781 -> Index Only Scan using minmaxtest3i on minmaxtest3
782 Index Cond: (f1 IS NOT NULL)
783 InitPlan 2 (returns $1)
786 Sort Key: minmaxtest_1.f1
787 -> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1
788 Index Cond: (f1 IS NOT NULL)
789 -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1
790 Index Cond: (f1 IS NOT NULL)
791 -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest2_1
792 Index Cond: (f1 IS NOT NULL)
793 -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1
794 Index Cond: (f1 IS NOT NULL)
797 select min(f1), max(f1) from minmaxtest;
803 -- DISTINCT doesn't do anything useful here, but it shouldn't fail
805 select distinct min(f1), max(f1) from minmaxtest;
807 ----------------------------------------------------------------------------------------------
809 InitPlan 1 (returns $0)
812 Sort Key: minmaxtest.f1
813 -> Index Only Scan using minmaxtesti on minmaxtest
814 Index Cond: (f1 IS NOT NULL)
815 -> Index Only Scan using minmaxtest1i on minmaxtest1
816 Index Cond: (f1 IS NOT NULL)
817 -> Index Only Scan Backward using minmaxtest2i on minmaxtest2
818 Index Cond: (f1 IS NOT NULL)
819 -> Index Only Scan using minmaxtest3i on minmaxtest3
820 Index Cond: (f1 IS NOT NULL)
821 InitPlan 2 (returns $1)
824 Sort Key: minmaxtest_1.f1
825 -> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1
826 Index Cond: (f1 IS NOT NULL)
827 -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1
828 Index Cond: (f1 IS NOT NULL)
829 -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest2_1
830 Index Cond: (f1 IS NOT NULL)
831 -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1
832 Index Cond: (f1 IS NOT NULL)
836 select distinct min(f1), max(f1) from minmaxtest;
842 drop table minmaxtest cascade;
843 NOTICE: drop cascades to 3 other objects
844 DETAIL: drop cascades to table minmaxtest1
845 drop cascades to table minmaxtest2
846 drop cascades to table minmaxtest3
847 -- check for correct detection of nested-aggregate errors
848 select max(min(unique1)) from tenk1;
849 ERROR: aggregate function calls cannot be nested
850 LINE 1: select max(min(unique1)) from tenk1;
852 select (select max(min(unique1)) from int8_tbl) from tenk1;
853 ERROR: aggregate function calls cannot be nested
854 LINE 1: select (select max(min(unique1)) from int8_tbl) from tenk1;
857 -- Test combinations of DISTINCT and/or ORDER BY
859 select array_agg(a order by b)
860 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
866 select array_agg(a order by a)
867 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
873 select array_agg(a order by a desc)
874 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
880 select array_agg(b order by a desc)
881 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
887 select array_agg(distinct a)
888 from (values (1),(2),(1),(3),(null),(2)) v(a);
894 select array_agg(distinct a order by a)
895 from (values (1),(2),(1),(3),(null),(2)) v(a);
901 select array_agg(distinct a order by a desc)
902 from (values (1),(2),(1),(3),(null),(2)) v(a);
908 select array_agg(distinct a order by a desc nulls last)
909 from (values (1),(2),(1),(3),(null),(2)) v(a);
915 -- multi-arg aggs, strict/nonstrict, distinct/order by
916 select aggfstr(a,b,c)
917 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
919 ---------------------------------------
920 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
924 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
926 -----------------------------------------------
927 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
930 select aggfstr(distinct a,b,c)
931 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
932 generate_series(1,3) i;
934 ---------------------------------------
935 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
938 select aggfns(distinct a,b,c)
939 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
940 generate_series(1,3) i;
942 -----------------------------------------------
943 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
946 select aggfstr(distinct a,b,c order by b)
947 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
948 generate_series(1,3) i;
950 ---------------------------------------
951 {"(3,1,baz)","(2,2,bar)","(1,3,foo)"}
954 select aggfns(distinct a,b,c order by b)
955 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
956 generate_series(1,3) i;
958 -----------------------------------------------
959 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
962 -- test specific code paths
963 select aggfns(distinct a,a,c order by c using ~<~,a)
964 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
965 generate_series(1,2) i;
967 ------------------------------------------------
968 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
971 select aggfns(distinct a,a,c order by c using ~<~)
972 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
973 generate_series(1,2) i;
975 ------------------------------------------------
976 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
979 select aggfns(distinct a,a,c order by a)
980 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
981 generate_series(1,2) i;
983 ------------------------------------------------
984 {"(0,0,)","(1,1,foo)","(2,2,bar)","(3,3,baz)"}
987 select aggfns(distinct a,b,c order by a,c using ~<~,b)
988 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
989 generate_series(1,2) i;
991 -----------------------------------------------
992 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
995 -- check node I/O via view creation and usage, also deparsing logic
996 create view agg_view1 as
998 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
999 select * from agg_view1;
1001 -----------------------------------------------
1002 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
1005 select pg_get_viewdef('agg_view1'::regclass);
1007 ---------------------------------------------------------------------------------------------------------------------
1008 SELECT aggfns(v.a, v.b, v.c) AS aggfns +
1009 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
1012 create or replace view agg_view1 as
1013 select aggfns(distinct a,b,c)
1014 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
1015 generate_series(1,3) i;
1016 select * from agg_view1;
1018 -----------------------------------------------
1019 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
1022 select pg_get_viewdef('agg_view1'::regclass);
1024 ---------------------------------------------------------------------------------------------------------------------
1025 SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns +
1026 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+
1027 generate_series(1, 3) i(i);
1030 create or replace view agg_view1 as
1031 select aggfns(distinct a,b,c order by b)
1032 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
1033 generate_series(1,3) i;
1034 select * from agg_view1;
1036 -----------------------------------------------
1037 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
1040 select pg_get_viewdef('agg_view1'::regclass);
1042 ---------------------------------------------------------------------------------------------------------------------
1043 SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns +
1044 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+
1045 generate_series(1, 3) i(i);
1048 create or replace view agg_view1 as
1049 select aggfns(a,b,c order by b+1)
1050 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
1051 select * from agg_view1;
1053 -----------------------------------------------
1054 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
1057 select pg_get_viewdef('agg_view1'::regclass);
1059 ---------------------------------------------------------------------------------------------------------------------
1060 SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns +
1061 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
1064 create or replace view agg_view1 as
1065 select aggfns(a,a,c order by b)
1066 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
1067 select * from agg_view1;
1069 ------------------------------------------------
1070 {"(3,3,baz)","(2,2,bar)","(1,1,foo)","(0,0,)"}
1073 select pg_get_viewdef('agg_view1'::regclass);
1075 ---------------------------------------------------------------------------------------------------------------------
1076 SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns +
1077 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
1080 create or replace view agg_view1 as
1081 select aggfns(a,b,c order by c using ~<~)
1082 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
1083 select * from agg_view1;
1085 -----------------------------------------------
1086 {"(2,2,bar)","(3,1,baz)","(1,3,foo)","(0,,)"}
1089 select pg_get_viewdef('agg_view1'::regclass);
1091 ---------------------------------------------------------------------------------------------------------------------
1092 SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns +
1093 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
1096 create or replace view agg_view1 as
1097 select aggfns(distinct a,b,c order by a,c using ~<~,b)
1098 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
1099 generate_series(1,2) i;
1100 select * from agg_view1;
1102 -----------------------------------------------
1103 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
1106 select pg_get_viewdef('agg_view1'::regclass);
1108 ---------------------------------------------------------------------------------------------------------------------
1109 SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns +
1110 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c),+
1111 generate_series(1, 2) i(i);
1114 drop view agg_view1;
1115 -- incorrect DISTINCT usage errors
1116 select aggfns(distinct a,b,c order by i)
1117 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1118 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1119 LINE 1: select aggfns(distinct a,b,c order by i)
1121 select aggfns(distinct a,b,c order by a,b+1)
1122 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1123 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1124 LINE 1: select aggfns(distinct a,b,c order by a,b+1)
1126 select aggfns(distinct a,b,c order by a,b,i,c)
1127 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1128 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1129 LINE 1: select aggfns(distinct a,b,c order by a,b,i,c)
1131 select aggfns(distinct a,a,c order by a,b)
1132 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1133 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1134 LINE 1: select aggfns(distinct a,a,c order by a,b)
1137 select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
1143 select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
1149 select string_agg(a,'AB') from (values(null),(null),('bbbb'),('cccc')) g(a);
1155 select string_agg(a,',') from (values(null),(null)) g(a);
1161 -- check some implicit casting cases, as per bug #5564
1162 select string_agg(distinct f1, ',' order by f1) from varchar_tbl; -- ok
1168 select string_agg(distinct f1::text, ',' order by f1) from varchar_tbl; -- not ok
1169 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1170 LINE 1: select string_agg(distinct f1::text, ',' order by f1) from v...
1172 select string_agg(distinct f1, ',' order by f1::text) from varchar_tbl; -- not ok
1173 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1174 LINE 1: select string_agg(distinct f1, ',' order by f1::text) from v...
1176 select string_agg(distinct f1::text, ',' order by f1::text) from varchar_tbl; -- ok
1182 -- string_agg bytea tests
1183 create table bytea_test_table(v bytea);
1184 select string_agg(v, '') from bytea_test_table;
1190 insert into bytea_test_table values(decode('ff','hex'));
1191 select string_agg(v, '') from bytea_test_table;
1197 insert into bytea_test_table values(decode('aa','hex'));
1198 select string_agg(v, '') from bytea_test_table;
1204 select string_agg(v, NULL) from bytea_test_table;
1210 select string_agg(v, decode('ee', 'hex')) from bytea_test_table;
1216 drop table bytea_test_table;
1218 select min(unique1) filter (where unique1 > 100) from tenk1;
1224 select ten, sum(distinct four) filter (where four::text ~ '123') from onek a
1240 select ten, sum(distinct four) filter (where four > 10) from onek a
1242 having exists (select 1 from onek b where sum(distinct a.four) = b.four);
1252 select max(foo COLLATE "C") filter (where (bar collate "POSIX") > '0')
1253 from (values ('a', 'b')) AS v(foo,bar);
1259 -- outer reference in FILTER (PostgreSQL extension)
1260 select (select count(*)
1261 from (values (1)) t0(inner_c))
1262 from (values (2),(3)) t1(outer_c); -- inner query is aggregation query
1269 select (select count(*) filter (where outer_c <> 0)
1270 from (values (1)) t0(inner_c))
1271 from (values (2),(3)) t1(outer_c); -- outer query is aggregation query
1277 select (select count(inner_c) filter (where outer_c <> 0)
1278 from (values (1)) t0(inner_c))
1279 from (values (2),(3)) t1(outer_c); -- inner query is aggregation query
1287 (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1))
1288 filter (where o.unique1 < 10))
1289 from tenk1 o; -- outer query is aggregation query
1295 -- subquery in FILTER clause (PostgreSQL extension)
1296 select sum(unique1) FILTER (WHERE
1297 unique1 IN (SELECT unique1 FROM onek where unique1 < 100)) FROM tenk1;
1303 -- exercise lots of aggregate parts with FILTER
1304 select aggfns(distinct a,b,c order by a,c using ~<~,b) filter (where a > 1)
1305 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
1306 generate_series(1,2) i;
1308 ---------------------------
1309 {"(2,2,bar)","(3,1,baz)"}
1312 -- variadic aggregates
1313 select least_agg(q1,q2) from int8_tbl;
1319 select least_agg(variadic array[q1,q2]) from int8_tbl;