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: aggregate functions are not allowed in WHERE
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: 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
718 Index Cond: (f1 IS NOT NULL)
719 -> Index Only Scan Backward using minmaxtest2i on minmaxtest2
720 Index Cond: (f1 IS NOT NULL)
721 -> Index Only Scan using minmaxtest3i on minmaxtest3
722 Index Cond: (f1 IS NOT NULL)
723 InitPlan 2 (returns $1)
726 Sort Key: minmaxtest_1.f1
727 -> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1
728 Index Cond: (f1 IS NOT NULL)
729 -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1
730 Index Cond: (f1 IS NOT NULL)
731 -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest2_1
732 Index Cond: (f1 IS NOT NULL)
733 -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1
734 Index Cond: (f1 IS NOT NULL)
737 select min(f1), max(f1) from minmaxtest;
743 -- DISTINCT doesn't do anything useful here, but it shouldn't fail
745 select distinct min(f1), max(f1) from minmaxtest;
747 ----------------------------------------------------------------------------------------------
749 InitPlan 1 (returns $0)
752 Sort Key: minmaxtest.f1
753 -> Index Only Scan using minmaxtesti on minmaxtest
754 Index Cond: (f1 IS NOT NULL)
755 -> Index Only Scan using minmaxtest1i on minmaxtest1
756 Index Cond: (f1 IS NOT NULL)
757 -> Index Only Scan Backward using minmaxtest2i on minmaxtest2
758 Index Cond: (f1 IS NOT NULL)
759 -> Index Only Scan using minmaxtest3i on minmaxtest3
760 Index Cond: (f1 IS NOT NULL)
761 InitPlan 2 (returns $1)
764 Sort Key: minmaxtest_1.f1
765 -> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1
766 Index Cond: (f1 IS NOT NULL)
767 -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1
768 Index Cond: (f1 IS NOT NULL)
769 -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest2_1
770 Index Cond: (f1 IS NOT NULL)
771 -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1
772 Index Cond: (f1 IS NOT NULL)
776 select distinct min(f1), max(f1) from minmaxtest;
782 drop table minmaxtest cascade;
783 NOTICE: drop cascades to 3 other objects
784 DETAIL: drop cascades to table minmaxtest1
785 drop cascades to table minmaxtest2
786 drop cascades to table minmaxtest3
787 -- check for correct detection of nested-aggregate errors
788 select max(min(unique1)) from tenk1;
789 ERROR: aggregate function calls cannot be nested
790 LINE 1: select max(min(unique1)) from tenk1;
792 select (select max(min(unique1)) from int8_tbl) from tenk1;
793 ERROR: aggregate function calls cannot be nested
794 LINE 1: select (select max(min(unique1)) from int8_tbl) from tenk1;
797 -- Test combinations of DISTINCT and/or ORDER BY
799 select array_agg(a order by b)
800 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
806 select array_agg(a order by a)
807 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
813 select array_agg(a order by a desc)
814 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
820 select array_agg(b order by a desc)
821 from (values (1,4),(2,3),(3,1),(4,2)) v(a,b);
827 select array_agg(distinct a)
828 from (values (1),(2),(1),(3),(null),(2)) v(a);
834 select array_agg(distinct a order by a)
835 from (values (1),(2),(1),(3),(null),(2)) v(a);
841 select array_agg(distinct a order by a desc)
842 from (values (1),(2),(1),(3),(null),(2)) v(a);
848 select array_agg(distinct a order by a desc nulls last)
849 from (values (1),(2),(1),(3),(null),(2)) v(a);
855 -- multi-arg aggs, strict/nonstrict, distinct/order by
856 select aggfstr(a,b,c)
857 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
859 ---------------------------------------
860 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
864 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
866 -----------------------------------------------
867 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
870 select aggfstr(distinct a,b,c)
871 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
872 generate_series(1,3) i;
874 ---------------------------------------
875 {"(1,3,foo)","(2,2,bar)","(3,1,baz)"}
878 select aggfns(distinct a,b,c)
879 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
880 generate_series(1,3) i;
882 -----------------------------------------------
883 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
886 select aggfstr(distinct a,b,c order by b)
887 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
888 generate_series(1,3) i;
890 ---------------------------------------
891 {"(3,1,baz)","(2,2,bar)","(1,3,foo)"}
894 select aggfns(distinct a,b,c order by b)
895 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
896 generate_series(1,3) i;
898 -----------------------------------------------
899 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
902 -- test specific code paths
903 select aggfns(distinct a,a,c order by c using ~<~,a)
904 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
905 generate_series(1,2) i;
907 ------------------------------------------------
908 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
911 select aggfns(distinct a,a,c order by c using ~<~)
912 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
913 generate_series(1,2) i;
915 ------------------------------------------------
916 {"(2,2,bar)","(3,3,baz)","(1,1,foo)","(0,0,)"}
919 select aggfns(distinct a,a,c order by a)
920 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
921 generate_series(1,2) i;
923 ------------------------------------------------
924 {"(0,0,)","(1,1,foo)","(2,2,bar)","(3,3,baz)"}
927 select aggfns(distinct a,b,c order by a,c using ~<~,b)
928 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
929 generate_series(1,2) i;
931 -----------------------------------------------
932 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
935 -- check node I/O via view creation and usage, also deparsing logic
936 create view agg_view1 as
938 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
939 select * from agg_view1;
941 -----------------------------------------------
942 {"(1,3,foo)","(0,,)","(2,2,bar)","(3,1,baz)"}
945 select pg_get_viewdef('agg_view1'::regclass);
947 ---------------------------------------------------------------------------------------------------------------------
948 SELECT aggfns(v.a, v.b, v.c) AS aggfns +
949 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
952 create or replace view agg_view1 as
953 select aggfns(distinct a,b,c)
954 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
955 generate_series(1,3) i;
956 select * from agg_view1;
958 -----------------------------------------------
959 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
962 select pg_get_viewdef('agg_view1'::regclass);
964 ----------------------------------------------------------------------------------------------------------------------
965 SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns +
966 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), +
967 generate_series(1, 3) i(i);
970 create or replace view agg_view1 as
971 select aggfns(distinct a,b,c order by b)
972 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
973 generate_series(1,3) i;
974 select * from agg_view1;
976 -----------------------------------------------
977 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
980 select pg_get_viewdef('agg_view1'::regclass);
982 ----------------------------------------------------------------------------------------------------------------------
983 SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns +
984 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), +
985 generate_series(1, 3) i(i);
988 create or replace view agg_view1 as
989 select aggfns(a,b,c order by b+1)
990 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
991 select * from agg_view1;
993 -----------------------------------------------
994 {"(3,1,baz)","(2,2,bar)","(1,3,foo)","(0,,)"}
997 select pg_get_viewdef('agg_view1'::regclass);
999 ---------------------------------------------------------------------------------------------------------------------
1000 SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns +
1001 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
1004 create or replace view agg_view1 as
1005 select aggfns(a,a,c order by b)
1006 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
1007 select * from agg_view1;
1009 ------------------------------------------------
1010 {"(3,3,baz)","(2,2,bar)","(1,1,foo)","(0,0,)"}
1013 select pg_get_viewdef('agg_view1'::regclass);
1015 ---------------------------------------------------------------------------------------------------------------------
1016 SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns +
1017 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
1020 create or replace view agg_view1 as
1021 select aggfns(a,b,c order by c using ~<~)
1022 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c);
1023 select * from agg_view1;
1025 -----------------------------------------------
1026 {"(2,2,bar)","(3,1,baz)","(1,3,foo)","(0,,)"}
1029 select pg_get_viewdef('agg_view1'::regclass);
1031 ---------------------------------------------------------------------------------------------------------------------
1032 SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns +
1033 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c);
1036 create or replace view agg_view1 as
1037 select aggfns(distinct a,b,c order by a,c using ~<~,b)
1038 from (values (1,3,'foo'),(0,null,null),(2,2,'bar'),(3,1,'baz')) v(a,b,c),
1039 generate_series(1,2) i;
1040 select * from agg_view1;
1042 -----------------------------------------------
1043 {"(0,,)","(1,3,foo)","(2,2,bar)","(3,1,baz)"}
1046 select pg_get_viewdef('agg_view1'::regclass);
1048 ----------------------------------------------------------------------------------------------------------------------
1049 SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns +
1050 FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), +
1051 generate_series(1, 2) i(i);
1054 drop view agg_view1;
1055 -- incorrect DISTINCT usage errors
1056 select aggfns(distinct a,b,c order by i)
1057 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1058 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1059 LINE 1: select aggfns(distinct a,b,c order by i)
1061 select aggfns(distinct a,b,c order by a,b+1)
1062 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1063 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1064 LINE 1: select aggfns(distinct a,b,c order by a,b+1)
1066 select aggfns(distinct a,b,c order by a,b,i,c)
1067 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1068 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1069 LINE 1: select aggfns(distinct a,b,c order by a,b,i,c)
1071 select aggfns(distinct a,a,c order by a,b)
1072 from (values (1,1,'foo')) v(a,b,c), generate_series(1,2) i;
1073 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1074 LINE 1: select aggfns(distinct a,a,c order by a,b)
1077 select string_agg(a,',') from (values('aaaa'),('bbbb'),('cccc')) g(a);
1083 select string_agg(a,',') from (values('aaaa'),(null),('bbbb'),('cccc')) g(a);
1089 select string_agg(a,'AB') from (values(null),(null),('bbbb'),('cccc')) g(a);
1095 select string_agg(a,',') from (values(null),(null)) g(a);
1101 -- check some implicit casting cases, as per bug #5564
1102 select string_agg(distinct f1, ',' order by f1) from varchar_tbl; -- ok
1108 select string_agg(distinct f1::text, ',' order by f1) from varchar_tbl; -- not ok
1109 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1110 LINE 1: select string_agg(distinct f1::text, ',' order by f1) from v...
1112 select string_agg(distinct f1, ',' order by f1::text) from varchar_tbl; -- not ok
1113 ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
1114 LINE 1: select string_agg(distinct f1, ',' order by f1::text) from v...
1116 select string_agg(distinct f1::text, ',' order by f1::text) from varchar_tbl; -- ok
1122 -- string_agg bytea tests
1123 create table bytea_test_table(v bytea);
1124 select string_agg(v, '') from bytea_test_table;
1130 insert into bytea_test_table values(decode('ff','hex'));
1131 select string_agg(v, '') from bytea_test_table;
1137 insert into bytea_test_table values(decode('aa','hex'));
1138 select string_agg(v, '') from bytea_test_table;
1144 select string_agg(v, NULL) from bytea_test_table;
1150 select string_agg(v, decode('ee', 'hex')) from bytea_test_table;
1156 drop table bytea_test_table;