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 -- SQL2003 binary aggregates
141 SELECT regr_count(b, a) FROM aggtest;
147 SELECT regr_sxx(b, a) FROM aggtest;
153 SELECT regr_syy(b, a) FROM aggtest;
159 SELECT regr_sxy(b, a) FROM aggtest;
165 SELECT regr_avgx(b, a), regr_avgy(b, a) FROM aggtest;
166 regr_avgx | regr_avgy
167 -----------+------------------
168 49.5 | 107.943152273074
171 SELECT regr_r2(b, a) FROM aggtest;
177 SELECT regr_slope(b, a), regr_intercept(b, a) FROM aggtest;
178 regr_slope | regr_intercept
179 -------------------+------------------
180 0.512750700441271 | 82.5619926012309
183 SELECT covar_pop(b, a), covar_samp(b, a) FROM aggtest;
184 covar_pop | covar_samp
185 -----------------+------------------
186 653.62895538751 | 871.505273850014
189 SELECT corr(b, a) FROM aggtest;
195 SELECT count(four) AS cnt_1000 FROM onek;
201 SELECT count(DISTINCT four) AS cnt_4 FROM onek;
207 select ten, count(*), sum(four) from onek
208 group by ten order by ten;
223 select ten, count(four), sum(DISTINCT four) from onek
224 group by ten order by ten;
239 -- user-defined aggregates
240 SELECT newavg(four) AS avg_1 FROM onek;
246 SELECT newsum(four) AS sum_1500 FROM onek;
252 SELECT newcnt(four) AS cnt_1000 FROM onek;
258 SELECT newcnt(*) AS cnt_1000 FROM onek;
264 SELECT oldcnt(*) AS cnt_1000 FROM onek;
270 SELECT sum2(q1,q2) FROM int8_tbl;
276 -- test for outer-level aggregates
278 select ten, sum(distinct four) from onek a
280 having exists (select 1 from onek b where sum(distinct a.four) = b.four);
290 -- this should fail because subquery has an agg of its own in WHERE
291 select ten, sum(distinct four) from onek a
293 having exists (select 1 from onek b
294 where sum(distinct a.four + b.four) = b.four);
295 ERROR: aggregates not allowed in WHERE clause
296 LINE 4: where sum(distinct a.four + b.four) = b.four)...
298 -- Test handling of sublinks within outer-level aggregates.
299 -- Per bug report from Daniel Grace.
301 (select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
309 -- test for bitwise integer aggregates
311 CREATE TEMPORARY TABLE bitwise_test(
329 COPY bitwise_test FROM STDIN NULL 'null';
336 BIT_AND(y) AS "0100",
344 1 | 1 | 1 | ? | 0 | 0100 | 7 | 7 | 7 | ? | 7 | 1101
345 ---+---+---+---+---+------+---+---+---+---+---+------
346 1 | 1 | 1 | 1 | 0 | 0100 | 7 | 7 | 7 | 3 | 7 | 1101
350 -- test boolean aggregates
352 -- first test all possible transition and final states
354 -- boolean and transitions
355 -- null because strict
356 booland_statefunc(NULL, NULL) IS NULL AS "t",
357 booland_statefunc(TRUE, NULL) IS NULL AS "t",
358 booland_statefunc(FALSE, NULL) IS NULL AS "t",
359 booland_statefunc(NULL, TRUE) IS NULL AS "t",
360 booland_statefunc(NULL, FALSE) IS NULL AS "t",
361 -- and actual computations
362 booland_statefunc(TRUE, TRUE) AS "t",
363 NOT booland_statefunc(TRUE, FALSE) AS "t",
364 NOT booland_statefunc(FALSE, TRUE) AS "t",
365 NOT booland_statefunc(FALSE, FALSE) AS "t";
366 t | t | t | t | t | t | t | t | t
367 ---+---+---+---+---+---+---+---+---
368 t | t | t | t | t | t | t | t | t
372 -- boolean or transitions
373 -- null because strict
374 boolor_statefunc(NULL, NULL) IS NULL AS "t",
375 boolor_statefunc(TRUE, NULL) IS NULL AS "t",
376 boolor_statefunc(FALSE, NULL) IS NULL AS "t",
377 boolor_statefunc(NULL, TRUE) IS NULL AS "t",
378 boolor_statefunc(NULL, FALSE) IS NULL AS "t",
379 -- actual computations
380 boolor_statefunc(TRUE, TRUE) AS "t",
381 boolor_statefunc(TRUE, FALSE) AS "t",
382 boolor_statefunc(FALSE, TRUE) AS "t",
383 NOT boolor_statefunc(FALSE, FALSE) AS "t";
384 t | t | t | t | t | t | t | t | t
385 ---+---+---+---+---+---+---+---+---
386 t | t | t | t | t | t | t | t | t
389 CREATE TEMPORARY TABLE bool_test(
404 COPY bool_test FROM STDIN NULL 'null';
410 BOOL_AND(NOT b2) AS "f",
411 BOOL_AND(NOT b3) AS "t"
413 f | t | f | n | f | t
414 ---+---+---+---+---+---
423 EVERY(NOT b2) AS "f",
426 f | t | f | n | f | t
427 ---+---+---+---+---+---
436 BOOL_OR(NOT b2) AS "f",
437 BOOL_OR(NOT b3) AS "t"
439 t | t | f | n | f | t
440 ---+---+---+---+---+---
445 -- Test cases that should be optimized into indexscans instead of
446 -- the generic aggregate implementation.
448 analyze tenk1; -- ensure we get consistent plans here
451 select min(unique1) from tenk1;
453 -------------------------------------------------------
455 InitPlan 1 (returns $0)
457 -> Index Scan using tenk1_unique1 on tenk1
458 Index Cond: (unique1 IS NOT NULL)
461 select min(unique1) from tenk1;
468 select max(unique1) from tenk1;
470 ----------------------------------------------------------------
472 InitPlan 1 (returns $0)
474 -> Index Scan Backward using tenk1_unique1 on tenk1
475 Index Cond: (unique1 IS NOT NULL)
478 select max(unique1) from tenk1;
485 select max(unique1) from tenk1 where unique1 < 42;
487 ------------------------------------------------------------------------
489 InitPlan 1 (returns $0)
491 -> Index Scan Backward using tenk1_unique1 on tenk1
492 Index Cond: ((unique1 IS NOT NULL) AND (unique1 < 42))
495 select max(unique1) from tenk1 where unique1 < 42;
502 select max(unique1) from tenk1 where unique1 > 42;
504 ------------------------------------------------------------------------
506 InitPlan 1 (returns $0)
508 -> Index Scan Backward using tenk1_unique1 on tenk1
509 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42))
512 select max(unique1) from tenk1 where unique1 > 42;
519 select max(unique1) from tenk1 where unique1 > 42000;
521 ---------------------------------------------------------------------------
523 InitPlan 1 (returns $0)
525 -> Index Scan Backward using tenk1_unique1 on tenk1
526 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000))
529 select max(unique1) from tenk1 where unique1 > 42000;
535 -- multi-column index (uses tenk1_thous_tenthous)
537 select max(tenthous) from tenk1 where thousand = 33;
539 --------------------------------------------------------------------------
541 InitPlan 1 (returns $0)
543 -> Index Scan Backward using tenk1_thous_tenthous on tenk1
544 Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL))
547 select max(tenthous) from tenk1 where thousand = 33;
554 select min(tenthous) from tenk1 where thousand = 33;
556 --------------------------------------------------------------------------
558 InitPlan 1 (returns $0)
560 -> Index Scan using tenk1_thous_tenthous on tenk1
561 Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL))
564 select min(tenthous) from tenk1 where thousand = 33;
570 -- check parameter propagation into an indexscan subquery
572 select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
575 -----------------------------------------------------------------------------------------
579 InitPlan 1 (returns $1)
581 -> Index Scan using tenk1_unique1 on tenk1
582 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > int4_tbl.f1))
585 select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
596 -- check some cases that were handled incorrectly in 8.3.0
598 select distinct max(unique2) from tenk1;
600 ----------------------------------------------------------------
602 InitPlan 1 (returns $0)
604 -> Index Scan Backward using tenk1_unique2 on tenk1
605 Index Cond: (unique2 IS NOT NULL)
609 select distinct max(unique2) from tenk1;
616 select max(unique2) from tenk1 order by 1;
618 ----------------------------------------------------------------
621 InitPlan 1 (returns $0)
623 -> Index Scan Backward using tenk1_unique2 on tenk1
624 Index Cond: (unique2 IS NOT NULL)
628 select max(unique2) from tenk1 order by 1;
635 select max(unique2) from tenk1 order by max(unique2);
637 ----------------------------------------------------------------
640 InitPlan 1 (returns $0)
642 -> Index Scan Backward using tenk1_unique2 on tenk1
643 Index Cond: (unique2 IS NOT NULL)
647 select max(unique2) from tenk1 order by max(unique2);
654 select max(unique2) from tenk1 order by max(unique2)+1;
656 ----------------------------------------------------------------
659 InitPlan 1 (returns $0)
661 -> Index Scan Backward using tenk1_unique2 on tenk1
662 Index Cond: (unique2 IS NOT NULL)
666 select max(unique2) from tenk1 order by max(unique2)+1;
673 select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
675 ----------------------------------------------------------------
677 Sort Key: (generate_series(1, 3))
678 InitPlan 1 (returns $0)
680 -> Index Scan Backward using tenk1_unique2 on tenk1
681 Index Cond: (unique2 IS NOT NULL)
685 select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
693 -- this is an interesting special case as of 9.1
695 select min(unique2) from tenk1 where unique2 = 42;
697 -----------------------------------------------
699 -> Index Scan using tenk1_unique2 on tenk1
700 Index Cond: (unique2 = 42)
703 select min(unique2) from tenk1 where unique2 = 42;
709 -- try it on an inheritance tree
710 create table minmaxtest(f1 int);
711 create table minmaxtest1() inherits (minmaxtest);
712 create table minmaxtest2() inherits (minmaxtest);
713 create index minmaxtesti on minmaxtest(f1);
714 create index minmaxtest1i on minmaxtest1(f1);
715 create index minmaxtest2i on minmaxtest2(f1 desc);
716 insert into minmaxtest values(11), (12);
717 insert into minmaxtest1 values(13), (14);
718 insert into minmaxtest2 values(15), (16);
720 select min(f1), max(f1) from minmaxtest;
722 --------------------------------------------------------------------------------------
724 InitPlan 1 (returns $0)
727 Sort Key: public.minmaxtest.f1
728 -> Index Scan using minmaxtesti on minmaxtest
729 Index Cond: (f1 IS NOT NULL)
730 -> Index Scan using minmaxtest1i on minmaxtest1 minmaxtest
731 Index Cond: (f1 IS NOT NULL)
732 -> Index Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest
733 Index Cond: (f1 IS NOT NULL)
734 InitPlan 2 (returns $1)
737 Sort Key: public.minmaxtest.f1
738 -> Index Scan Backward using minmaxtesti on minmaxtest
739 Index Cond: (f1 IS NOT NULL)
740 -> Index Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest
741 Index Cond: (f1 IS NOT NULL)
742 -> Index Scan using minmaxtest2i on minmaxtest2 minmaxtest
743 Index Cond: (f1 IS NOT NULL)
746 select min(f1), max(f1) from minmaxtest;
752 drop table minmaxtest cascade;
753 NOTICE: drop cascades to 2 other objects
754 DETAIL: drop cascades to table minmaxtest1
755 drop cascades to table minmaxtest2
757 -- Test combinations of DISTINCT and/or ORDER BY
759 select array_agg(a order by b)
760 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
766 select array_agg(a order by a)
767 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
773 select array_agg(a order by a desc)
774 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
780 select array_agg(b order by a desc)
781 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
787 select array_agg(distinct a)
788 from (values (1),(2),(1),(3),(null),(2)) v(a);
794 select array_agg(distinct a order by a)
795 from (values (1),(2),(1),(3),(null),(2)) v(a);
801 select array_agg(distinct a order by a desc)
802 from (values (1),(2),(1),(3),(null),(2)) v(a);
808 select array_agg(distinct a order by a desc nulls last)
809 from (values (1),(2),(1),(3),(null),(2)) v(a);
815 -- multi-arg aggs, strict/nonstrict, distinct/order by
816 select aggfstr(a,b,c)
817 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
819 ---------------------------------------
820 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
824 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
826 -----------------------------------------------
827 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
830 select aggfstr(distinct a,b,c)
831 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
832 generate_series(1,3) i;
834 ---------------------------------------
835 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
838 select aggfns(distinct a,b,c)
839 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
840 generate_series(1,3) i;
842 -----------------------------------------------
843 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
846 select aggfstr(distinct a,b,c order by b)
847 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
848 generate_series(1,3) i;
850 ---------------------------------------
851 {"(3,1,baz)","(2,2,bar)","(1,3,foo)"}
854 select aggfns(distinct a,b,c order by b)
855 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
856 generate_series(1,3) i;
858 -----------------------------------------------
859 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
862 -- test specific code paths
863 select aggfns(distinct a,a,c order by c using ~<~,a)
864 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
865 generate_series(1,2) i;
867 ------------------------------------------------
868 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
871 select aggfns(distinct a,a,c order by c using ~<~)
872 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
873 generate_series(1,2) i;
875 ------------------------------------------------
876 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
879 select aggfns(distinct a,a,c order by a)
880 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
881 generate_series(1,2) i;
883 ------------------------------------------------
884 {"(0,0,)","(1,1,foo)","(2,2,bar)","(3,3,baz)"}
887 select aggfns(distinct a,b,c order by a,c using ~<~,b)
888 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
889 generate_series(1,2) i;
891 -----------------------------------------------
892 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
895 -- check node I/O via view creation and usage, also deparsing logic
896 create view agg_view1 as
898 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
899 select * from agg_view1;
901 -----------------------------------------------
902 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
905 select pg_get_viewdef('agg_view1'::regclass);
907 --------------------------------------------------------------------------------------------------------------------------------------------------------
908 SELECT aggfns(v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
911 create or replace view agg_view1 as
912 select aggfns(distinct a,b,c)
913 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
914 generate_series(1,3) i;
915 select * from agg_view1;
917 -----------------------------------------------
918 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
921 select pg_get_viewdef('agg_view1'::regclass);
923 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
924 SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i);
927 create or replace view agg_view1 as
928 select aggfns(distinct a,b,c order by b)
929 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
930 generate_series(1,3) i;
931 select * from agg_view1;
933 -----------------------------------------------
934 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
937 select pg_get_viewdef('agg_view1'::regclass);
939 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
940 SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i);
943 create or replace view agg_view1 as
944 select aggfns(a,b,c order by b+1)
945 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
946 select * from agg_view1;
948 -----------------------------------------------
949 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
952 select pg_get_viewdef('agg_view1'::regclass);
954 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
955 SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
958 create or replace view agg_view1 as
959 select aggfns(a,a,c order by b)
960 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
961 select * from agg_view1;
963 ------------------------------------------------
964 {"(3,3,baz)","(2,2,bar)","(1,1,foo)","(0,0,)"}
967 select pg_get_viewdef('agg_view1'::regclass);
969 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
970 SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
973 create or replace view agg_view1 as
974 select aggfns(a,b,c order by c using ~<~)
975 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
976 select * from agg_view1;
978 -----------------------------------------------
979 {"(2,2,bar)","(3,1,baz)","(1,3,foo)","(0,,)"}
982 select pg_get_viewdef('agg_view1'::regclass);
984 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
985 SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
988 create or replace view agg_view1 as
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;
992 select * from agg_view1;
994 -----------------------------------------------
995 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
998 select pg_get_viewdef('agg_view1'::regclass);
1000 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1001 SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 2) i(i);
1004 drop view agg_view1;
1005 -- incorrect DISTINCT usage errors
1006 select aggfns(distinct a,b,c order by i)
1007 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1008 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1009 LINE 1: select aggfns(distinct a,b,c order by i)
1011 select aggfns(distinct a,b,c order by a,b+1)
1012 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1013 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1014 LINE 1: select aggfns(distinct a,b,c order by a,b+1)
1016 select aggfns(distinct a,b,c order by a,b,i,c)
1017 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1018 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1019 LINE 1: select aggfns(distinct a,b,c order by a,b,i,c)
1021 select aggfns(distinct a,a,c order by a,b)
1022 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1023 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1024 LINE 1: select aggfns(distinct a,a,c order by a,b)
1027 select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
1033 select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
1039 select string_agg(a,'AB') from (values(null),(null),('bbbb'),('cccc')) g(a);
1045 select string_agg(a,',') from (values(null),(null)) g(a);
1051 -- check some implicit casting cases, as per bug #5564
1052 select string_agg(distinct f1, ',' order by f1) from varchar_tbl; -- ok
1058 select string_agg(distinct f1::text, ',' order by f1) from varchar_tbl; -- not ok
1059 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1060 LINE 1: select string_agg(distinct f1::text, ',' order by f1) from v...
1062 select string_agg(distinct f1, ',' order by f1::text) from varchar_tbl; -- not ok
1063 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1064 LINE 1: select string_agg(distinct f1, ',' order by f1::text) from v...
1066 select string_agg(distinct f1::text, ',' order by f1::text) from varchar_tbl; -- ok