The EDB Blog
December 19, 2019

 

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.

1. Oracle LISTAGG Function

         a. LISTAGG Syntax

         b. LISTAGG Function Types

2. LISTAGG Compatibility Oracle Vs. PostgreSQL Vs. EDB Postgres Advanced Server

         a. LISTAGG function support in PostgreSQL

         b. LISTAGG function support in EDB Postgre Advanced Server

3. Example of LISTAGG(...) WITHIN GROUP (ORDER BY…)

4. Example of LISTAGG(...) WITHIN GROUP (ORDER BY ...) OVER (PARTITION BY ...)

 

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

説明

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. 

 

raghavendra.rao_enterprisedb.com's picture

Raghavendra Rao is a Managing Consultant in Professional Services Division in EnterpriseDB(Headquarters). He has been working with PostgreSQL since 2009 and over 20 years of working experience with relation database systems. He architects, migrates, manages, consults, speaks, teaches & blogs...