This blog discusses the LISTAGG function in Oracle and how to achieve similar functionality in PostgreSQL 12 and EDB Postgres Advanced Server. This is one of the most common challenges enterprises face when migrating from Oracle to PostgreSQL.
The LISTAGG function in Oracle
The Oracle LISTAGG() function is an aggregation function that transforms data from multiple rows into a single list of values separated by a specified delimiter. The Oracle LISTAGG function is typically used to denormalize values from multiple rows into a single value, which can be a list of comma-separated values (CSV) or other human-readable format for reporting purposes.
Oracle LISTAGG Syntax
LISTAGG(measure_expr [, 'delimiter'])
WITHIN GROUP (order_by_clause) [OVER query_partition_clause]
For the Oracle LISTAGG function, the return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2. So, if any string aggregation is beyond the specified data type, it may error out as a “result of string concatenation is too long”.
Oracle LISTAGG function types
Types | Description |
---|---|
LISTAGG(...) WITHIN GROUP (ORDER BY ...) | The LISTAGG column is aggregated with a delimiter-separated list. Using WITH GROUP will return one row-per-group. |
LISTAGG(... ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY ...) | If the concatenation string is longer than the return data type, then there will be an "ORA-01489" error. Using the ON OVERFLOW ERROR clause will handle errors gracefully. |
LISTAGG(... ON OVERFLOW TRUNCATE ) WITHIN GROUP (ORDER BY ...) | The ON OVERFLOW TRUNCATE clause not only handles overflow errors gracefully but it will also add a literal ellipsis ('...') and a count. |
LISTAGG(... ON OVERFLOW TRUNCATE ‘...’ ) WITHIN GROUP (ORDER BY ...) | We can specify our own truncate literal (‘...’) if we don't want to use the default ellipsis. |
LISTAGG(... ON OVERFLOW TRUNCATE ‘...’ WITHOUT COUNT ) WITHIN GROUP (ORDER BY ...) | We can also omit the count by adding WITHOUT COUNT. The default is the equivalent of explicitly using WITH COUNT. |
LISTAGG(...) WITHIN GROUP (ORDER BY ...) OVER (PARTITION BY ...) | The OVER (PARTITION BY) clause will return all the rows and duplicate the LISTAGG result for each row in the partition. |
Now, let’s see what kind of similar support we have in PostgreSQL 12 and EDB Postgres Advanced Server 12.
LISTAGG Compatibility Oracle Vs. PostgreSQL Vs. EDB Postgres Advanced Server
This compatibility chart provides a high-level picture of what is supported in PostgreSQL compared to the EDB Postgres Advanced Server.
Oracle 12c LISTAGG function models | Support in PostgreSQL 12 | Support in EDB Postgres Advanced Server 12 |
---|---|---|
LISTAGG(...) WITHIN GROUP (ORDER BY ...) | STRING_AGG(... ORDER BY ...) OR ARRAY_AGG(... ORDER BY ..) | Same as Oracle |
LISTAGG(... ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY ...) | Not available | Not available |
LISTAGG(... ON OVERFLOW TRUNCATE ) WITHIN GROUP (ORDER BY ...) | Not available | Not available |
LISTAGG(... ON OVERFLOW TRUNCATE ‘...’ ) WITHIN GROUP (ORDER BY ...) | Not available | Not available |
LISTAGG(... ON OVERFLOW TRUNCATE ‘...’ WITHOUT COUNT ) WITHIN GROUP (ORDER BY ...) | Not available | Not available |
LISTAGG(...) WITHIN GROUP (ORDER BY ...) OVER (PARTITION BY ...) | STRING_AGG(...) OVER (PARTITION BY ... ORDER BY ...) | Same as Oracle |
LISTAGG function support in PostgreSQL
Like other databases, PostgreSQL also has many string aggregation expressions and functions with order_by_clause and filter_clause. For string aggregation like Oracle LISTAGG, there are two functions, STRING_AGG and ARRAY_AGG. These don’t cover all the functionalities in Oracle LISTAGG, but do offer the fundamental functionality of string aggregation.
The PostgreSQL ARRAY_AGG function can be used for string aggregation, but we need to make another call on top of it to convert the aggregation result set from ARRAY to STRING with the ARRAY_TO_STRING function. There will be a slight performance overhead if there’s a big aggregation result set, so the use of the STRING_AGG function is recommended instead.
The PostgreSQL STRING_AGG function has limited functionality, and it does not cover the handling of a “result of string concatenation is too long” error like in the Oracle clauses ON OVERFLOW ERROR, ON OVERFLOW TRUNCATE, or ON OVERFLOW TRUNCATE ... WITHOUT COUNT. However, the return type of STRING_AGG is BYTEA/TEXT data type and big enough to handle a large string aggregation result set.
PostgreSQL Syntax
postgres=# \df string_agg
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------+------------------+---------------------+------
pg_catalog | string_agg | bytea | bytea, bytea | agg
pg_catalog | string_agg | text | text, text | agg
(2 rows)
LISTAGG function in EDB Postgres Advanced Server (Oracle Compatibility database)
Latest EDB Postgres Advanced Server (Oracle Compatibility) version 12, supports two aggregate functions: LISTAGG and MEDIAN.
The EDB Postgres Advanced Server is an enhanced version of open source PostgreSQL with various added functionalities along with Oracle database compatibility. The Oracle database compatibility layer in EDB Postgres Advanced Server directly supports the LISTAGG function, which means we can execute the same Oracle function calls in the EDB Postgres Advanced Server without any change.
EDB Postgres Advanced Server Syntax
LISTAGG( measure_expr [, delimiter ])
WITHIN GROUP( order_by_clause ) [ OVER query_partition_by_clause ]
Let’s run some of the supported functionalities in PostgreSQL and EDB Postgres Advanced Server for comparison.
Example of LISTAGG(...) WITHIN GROUP (ORDER BY ...)
In Oracle
SQL> SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno ORDER BY deptno;
DEPTNO EMPLOYEES
---------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
In PostgreSQL 12 (Using STRING_AGG & ARRAY_AGG)
postgres=# SELECT deptno, STRING_AGG(ename, ',' ORDER BY ename) AS employees
FROM emp
GROUP BY deptno ;
deptno | employees
--------+--------------------------------------
10 | CLARK,KING,MILLER
20 | ADAMS,FORD,JONES,SCOTT,SMITH
30 | ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
(3 rows)
-- ARRAY_AGG model
postgres=# select deptno,array_agg(ename order by ename) from emp group by deptno;
deptno | array_agg
--------+----------------------------------------
10 | {CLARK,KING,MILLER}
20 | {ADAMS,FORD,JONES,SCOTT,SMITH}
30 | {ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD}
(3 rows)
-- Converting ARRAY_AGG result to string using ARRAY_TO_STRING
postgres=# select deptno,array_to_string(array_agg(ename order by ename),',') from emp group by deptno;
deptno | array_to_string
--------+--------------------------------------
10 | CLARK,KING,MILLER
20 | ADAMS,FORD,JONES,SCOTT,SMITH
30 | ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
(3 rows)
In EDB Postgres Advanced Server
edb=# SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
edb-# FROM emp
edb-# GROUP BY deptno
edb-# ORDER BY deptno;
deptno | employees
--------+--------------------------------------
10 | CLARK,KING,MILLER
20 | ADAMS,FORD,JONES,SCOTT,SMITH
30 | ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
(3 rows)
Example of LISTAGG(...) WITHIN GROUP (ORDER BY ...) OVER (PARTITION BY ...)
In Oracle
SQL> SELECT distinct DEPTNO,
LISTAGG( ENAME, ',' ) WITHIN GROUP (ORDER BY ENAME) OVER(PARTITION BY DEPTNO) as employees
FROM EMP
order by deptno;
DEPTNO EMPLOYEES
---------- ---------------------------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
In PostgreSQL 12
postgres=# SELECT distinct DEPTNO,STRING_AGG( ENAME, ',') OVER(PARTITION BY DEPTNO) as employees
FROM EMP
group by deptno,ename
order by deptno;
deptno | employees
--------+--------------------------------------
10 | KING,MILLER,CLARK
20 | SCOTT,ADAMS,FORD,JONES,SMITH
30 | WARD,BLAKE,ALLEN,MARTIN,TURNER,JAMES
(3 rows)
In EDB Postgres
edb=# SELECT distinct DEPTNO,
edb-# LISTAGG( ENAME, ',' ) WITHIN GROUP (ORDER BY ENAME) OVER(PARTITION BY DEPTNO) as employees
edb-# FROM EMP
edb-# order by deptno;
deptno | employees
--------+--------------------------------------
10 | CLARK,KING,MILLER
20 | ADAMS,FORD,JONES,SCOTT,SMITH
30 | ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
(3 rows)
While moving from Oracle to Postgres isn't always straight forward, EDB Postgres Advanced Server makes it easier to migrate. If you need assistance handling more complex migration issues, connect with EDB support services. We can provide you with a migration assessment and help you migrate to PostgreSQL or EDB Postgres Advanced Server.