How to workaround Oracle LISTAGG function in 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]

Reference Link

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.

EDB Postgres Release Notes

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.