gp_array_agg
gp_array_agg
The gp_array_agg module introduces a parallel array_agg() aggregate function that you can use in Greenplum Database.
The gp_array_agg module is a Greenplum Database extension.
Installing and Registering the Module
The gp_array_agg module is installed when you install Greenplum Database. Before you can use the aggregate function defined in the module, you must register the gp_array_agg extension in each database where you want to use the function:
CREATE EXTENSION gp_array_agg;
Refer to Installing Additional Supplied Modules for more information.
Using the Module
The gp_array_agg() function has the following signature:
gp_array_agg( anyelement )
You can use the function to create an array from input values, including nulls. For example:
SELECT gp_array_agg(a) FROM t1; gp_array_agg ------------------ {2,1,3,NULL,1,2} (1 row)
gp_array_agg() assigns each input value to an array element, and then returns the array. The function returns null rather than an empty array when there are no input rows.
gp_array_agg() produces results that depend on the ordering of the input rows. The ordering is unspecified by default; you can control the ordering by specifying an ORDER BY clause within the aggregate. For example:
CREATE TABLE table1(a int4, b int4); INSERT INTO table1 VALUES (4,5), (2,1), (1,3), (3,null), (3,7); SELECT gp_array_agg(a ORDER BY b NULLS FIRST) FROM table1; gp_array_agg -------------- {3,2,1,4,7} (1 row)
Additional Module Documentation
Refer to Aggregate Functions in the PostgreSQL documentation for more information about aggregates.