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 Only 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 Only 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 Only 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 Only 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 Only 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 Only 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 Only 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 Only 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 Only 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 Only 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 Only 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 Only 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 Only 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 -- try it on an inheritance tree
694 create table minmaxtest(f1 int);
695 create table minmaxtest1() inherits (minmaxtest);
696 create table minmaxtest2() inherits (minmaxtest);
697 create table minmaxtest3() inherits (minmaxtest);
698 create index minmaxtesti on minmaxtest(f1);
699 create index minmaxtest1i on minmaxtest1(f1);
700 create index minmaxtest2i on minmaxtest2(f1 desc);
701 create index minmaxtest3i on minmaxtest3(f1) where f1 is not null;
702 insert into minmaxtest values(11), (12);
703 insert into minmaxtest1 values(13), (14);
704 insert into minmaxtest2 values(15), (16);
705 insert into minmaxtest3 values(17), (18);
707 select min(f1), max(f1) from minmaxtest;
709 -------------------------------------------------------------------------------------------
711 InitPlan 1 (returns $0)
714 Sort Key: public.minmaxtest.f1
715 -> Index Only Scan using minmaxtesti on minmaxtest
716 Index Cond: (f1 IS NOT NULL)
717 -> Index Only Scan using minmaxtest1i on minmaxtest1 minmaxtest
718 Index Cond: (f1 IS NOT NULL)
719 -> Index Only Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest
720 Index Cond: (f1 IS NOT NULL)
721 -> Index Only Scan using minmaxtest3i on minmaxtest3 minmaxtest
722 Index Cond: (f1 IS NOT NULL)
723 InitPlan 2 (returns $1)
726 Sort Key: public.minmaxtest.f1
727 -> Index Only Scan Backward using minmaxtesti on minmaxtest
728 Index Cond: (f1 IS NOT NULL)
729 -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest
730 Index Cond: (f1 IS NOT NULL)
731 -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest
732 Index Cond: (f1 IS NOT NULL)
733 -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest
734 Index Cond: (f1 IS NOT NULL)
737 select min(f1), max(f1) from minmaxtest;
743 drop table minmaxtest cascade;
744 NOTICE: drop cascades to 3 other objects
745 DETAIL: drop cascades to table minmaxtest1
746 drop cascades to table minmaxtest2
747 drop cascades to table minmaxtest3
749 -- Test combinations of DISTINCT and/or ORDER BY
751 select array_agg(a order by b)
752 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
758 select array_agg(a order by a)
759 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
765 select array_agg(a order by a desc)
766 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
772 select array_agg(b order by a desc)
773 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
779 select array_agg(distinct a)
780 from (values (1),(2),(1),(3),(null),(2)) v(a);
786 select array_agg(distinct a order by a)
787 from (values (1),(2),(1),(3),(null),(2)) v(a);
793 select array_agg(distinct a order by a desc)
794 from (values (1),(2),(1),(3),(null),(2)) v(a);
800 select array_agg(distinct a order by a desc nulls last)
801 from (values (1),(2),(1),(3),(null),(2)) v(a);
807 -- multi-arg aggs, strict/nonstrict, distinct/order by
808 select aggfstr(a,b,c)
809 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
811 ---------------------------------------
812 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
816 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
818 -----------------------------------------------
819 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
822 select aggfstr(distinct a,b,c)
823 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
824 generate_series(1,3) i;
826 ---------------------------------------
827 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
830 select aggfns(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 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
838 select aggfstr(distinct a,b,c order by b)
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 {"(3,1,baz)","(2,2,bar)","(1,3,foo)"}
846 select aggfns(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)","(0,,)"}
854 -- test specific code paths
855 select aggfns(distinct a,a,c order by c using ~<~,a)
856 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
857 generate_series(1,2) i;
859 ------------------------------------------------
860 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
863 select aggfns(distinct a,a,c order by c using ~<~)
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 a)
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 {"(0,0,)","(1,1,foo)","(2,2,bar)","(3,3,baz)"}
879 select aggfns(distinct a,b,c order by a,c using ~<~,b)
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,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
887 -- check node I/O via view creation and usage, also deparsing logic
888 create view agg_view1 as
890 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
891 select * from agg_view1;
893 -----------------------------------------------
894 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
897 select pg_get_viewdef('agg_view1'::regclass);
899 --------------------------------------------------------------------------------------------------------------------------------------------------------
900 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);
903 create or replace view agg_view1 as
904 select aggfns(distinct a,b,c)
905 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
906 generate_series(1,3) i;
907 select * from agg_view1;
909 -----------------------------------------------
910 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
913 select pg_get_viewdef('agg_view1'::regclass);
915 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
916 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);
919 create or replace view agg_view1 as
920 select aggfns(distinct a,b,c order by b)
921 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
922 generate_series(1,3) i;
923 select * from agg_view1;
925 -----------------------------------------------
926 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
929 select pg_get_viewdef('agg_view1'::regclass);
931 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
932 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);
935 create or replace view agg_view1 as
936 select aggfns(a,b,c order by b+1)
937 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
938 select * from agg_view1;
940 -----------------------------------------------
941 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
944 select pg_get_viewdef('agg_view1'::regclass);
946 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
947 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);
950 create or replace view agg_view1 as
951 select aggfns(a,a,c order by b)
952 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
953 select * from agg_view1;
955 ------------------------------------------------
956 {"(3,3,baz)","(2,2,bar)","(1,1,foo)","(0,0,)"}
959 select pg_get_viewdef('agg_view1'::regclass);
961 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
962 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);
965 create or replace view agg_view1 as
966 select aggfns(a,b,c order by c using ~<~)
967 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
968 select * from agg_view1;
970 -----------------------------------------------
971 {"(2,2,bar)","(3,1,baz)","(1,3,foo)","(0,,)"}
974 select pg_get_viewdef('agg_view1'::regclass);
976 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
977 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);
980 create or replace view agg_view1 as
981 select aggfns(distinct a,b,c order by a,c using ~<~,b)
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;
984 select * from agg_view1;
986 -----------------------------------------------
987 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
990 select pg_get_viewdef('agg_view1'::regclass);
992 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
993 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);
997 -- incorrect DISTINCT usage errors
998 select aggfns(distinct a,b,c order by i)
999 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1000 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1001 LINE 1: select aggfns(distinct a,b,c order by i)
1003 select aggfns(distinct a,b,c order by a,b+1)
1004 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1005 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1006 LINE 1: select aggfns(distinct a,b,c order by a,b+1)
1008 select aggfns(distinct a,b,c order by a,b,i,c)
1009 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1010 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1011 LINE 1: select aggfns(distinct a,b,c order by a,b,i,c)
1013 select aggfns(distinct a,a,c order by a,b)
1014 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1015 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1016 LINE 1: select aggfns(distinct a,a,c order by a,b)
1019 select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
1025 select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
1031 select string_agg(a,'AB') from (values(null),(null),('bbbb'),('cccc')) g(a);
1037 select string_agg(a,',') from (values(null),(null)) g(a);
1043 -- check some implicit casting cases, as per bug #5564
1044 select string_agg(distinct f1, ',' order by f1) from varchar_tbl; -- ok
1050 select string_agg(distinct f1::text, ',' order by f1) from varchar_tbl; -- not ok
1051 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1052 LINE 1: select string_agg(distinct f1::text, ',' order by f1) from v...
1054 select string_agg(distinct f1, ',' order by f1::text) from varchar_tbl; -- not ok
1055 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1056 LINE 1: select string_agg(distinct f1, ',' order by f1::text) from v...
1058 select string_agg(distinct f1::text, ',' order by f1::text) from varchar_tbl; -- ok
1064 -- string_agg bytea tests
1065 create table bytea_test_table(v bytea);
1066 select string_agg(v, '') from bytea_test_table;
1072 insert into bytea_test_table values(decode('ff','hex'));
1073 select string_agg(v, '') from bytea_test_table;
1079 insert into bytea_test_table values(decode('aa','hex'));
1080 select string_agg(v, '') from bytea_test_table;
1086 select string_agg(v, NULL) from bytea_test_table;
1092 select string_agg(v, decode('ee', 'hex')) from bytea_test_table;
1098 drop table bytea_test_table;