Обсуждение: LISTAGG à la Oracle in PostgreSQL

Hello,

I can write a LISTAGG aggregate for:

create table emp(deptno numeric, ename text);

SELECT deptno, LISTAGG(ename, ','::text ORDER BY ename) AS employees 
FROM   emp GROUP BY deptno ORDER BY deptno;

I would like to know if is possible to create an aggregate LISTAGG that 
would work like in Oracle:

SELECT deptno,
        listagg(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno
ORDER BY deptno;

I failed and IA also failed. Claude says:

It is not possible to exactly replicate listagg(ename, ',') WITHIN GROUP 
(ORDER BY ename) as a custom PostgreSQL aggregate
because PostgreSQL strictly forbids ungrouped columns as direct 
arguments to ordered-set aggregates.

Do you agree ?

On Mon, Mar 9, 2026 at 1:21 PM Pierre Forstmann
<pierre.forstmann@gmail.com> wrote:
>
> Hello,
>
> I can write a LISTAGG aggregate for:
>
> create table emp(deptno numeric, ename text);
>
> SELECT deptno, LISTAGG(ename, ','::text ORDER BY ename) AS employees
> FROM   emp GROUP BY deptno ORDER BY deptno;
>
> I would like to know if is possible to create an aggregate LISTAGG that
> would work like in Oracle:
>
> SELECT deptno,
>         listagg(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
> FROM emp
> GROUP BY deptno
> ORDER BY deptno;

I don't think you need a custom aggregate here. In Postgres you can say:

select deptno,
      string_agg(ename, ',' ORDER BY ename) AS employees
FROM emp
GROUP BY deptno
ORDER BY deptno;

--
Paul              ~{:-)
pj@illuminatedcomputing.com

I agree but I just would like to know if there is way to be compatible 
with Oracle syntax using aggregate features in PostgreSQL

Thanks.

Le 09/03/2026 à 23:05, Paul A Jungwirth a écrit :
> On Mon, Mar 9, 2026 at 1:21 PM Pierre Forstmann
> <pierre.forstmann@gmail.com> wrote:
>> Hello,
>>
>> I can write a LISTAGG aggregate for:
>>
>> create table emp(deptno numeric, ename text);
>>
>> SELECT deptno, LISTAGG(ename, ','::text ORDER BY ename) AS employees
>> FROM   emp GROUP BY deptno ORDER BY deptno;
>>
>> I would like to know if is possible to create an aggregate LISTAGG that
>> would work like in Oracle:
>>
>> SELECT deptno,
>>          listagg(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
>> FROM emp
>> GROUP BY deptno
>> ORDER BY deptno;
> I don't think you need a custom aggregate here. In Postgres you can say:
>
> select deptno,
>        string_agg(ename, ',' ORDER BY ename) AS employees
> FROM emp
> GROUP BY deptno
> ORDER BY deptno;
>

On 09.03.26 21:21, Pierre Forstmann wrote:
> Hello,
> 
> I can write a LISTAGG aggregate for:
> 
> create table emp(deptno numeric, ename text);
> 
> SELECT deptno, LISTAGG(ename, ','::text ORDER BY ename) AS employees 
> FROM   emp GROUP BY deptno ORDER BY deptno;
> 
> I would like to know if is possible to create an aggregate LISTAGG that 
> would work like in Oracle:
> 
> SELECT deptno,
>         listagg(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
> FROM emp
> GROUP BY deptno
> ORDER BY deptno;
> 
> I failed and IA also failed. Claude says:
> 
> It is not possible to exactly replicate listagg(ename, ',') WITHIN GROUP 
> (ORDER BY ename) as a custom PostgreSQL aggregate
> because PostgreSQL strictly forbids ungrouped columns as direct 
> arguments to ordered-set aggregates.
> 
> Do you agree ?

One of the reasons that PostgreSQL hasn't implemented LISTAGG is that it 
is a misdesign.  It uses ordered-set aggregate syntax even
though it is not very similar to the other ordered-set aggregates.
Its syntax should be more similar to ARRAY_AGG or
JSON_ARRAYAGG, for example.  But it's too late to fix the standard on this.

Actually I'm trying to do for IvorySQL.

On 10/03/2026 21:15, Isaac Morland <isaac.morland@gmail.com> wrote:
> On Tue, 10 Mar 2026 at 15:57, Juan Rodrigo Alejandro Burgos Mella 
> <rodrigoburgosmella@gmail.com <mailto:rodrigoburgosmella@gmail.com>> wrote:
> 
>     To do something similar, you would have to fork the source code and
>     implement the declarations with the same syntax, resulting in
>     something like Postracle.
> 
> 
> Is this the sort of thing IvorySQL could help with?
> 

Thanks.

On 10/03/2026 22:46, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 
> 
> út 10. 3. 2026 v 21:23 odesílatel Pavel Stehule <pavel.stehule@gmail.com 
> <mailto:pavel.stehule@gmail.com>> napsal:
> 
>     Hi
> 
>     út 10. 3. 2026 v 20:58 odesílatel Juan Rodrigo Alejandro Burgos
>     Mella <rodrigoburgosmella@gmail.com
>     <mailto:rodrigoburgosmella@gmail.com>> napsal:
> 
>         To do something similar, you would have to fork the source code
>         and implement the declarations with the same syntax, resulting
>         in something like Postracle.
> 
> 
>     orafce has listagg function https://github.com/orafce/orafce
>     <https://github.com/orafce/orafce>
> 
> 
> but it doesn't support syntax WITHING GROUP syntax. Probably there is 
> not a possibility to implement it in extension without introducing a new 
> kind of aggregate functions in core, or enhancing behaviour of ordered- 
> set kind of aggregates.
> 
> Regards
> 
> Pavel
> 
> 
> 
> 
> 
>     Regards
> 
>     Pavel
> 
> 
>         Atte
>         JRBM
> 
>         El mar, 10 mar 2026 a las 13:53, Pierre Forstmann
>         (<pierre.forstmann@gmail.com
>         <mailto:pierre.forstmann@gmail.com>>) escribió:
> 
>             I agree but I just would like to know if there is way to be
>             compatible
>             with Oracle syntax using aggregate features in PostgreSQL
> 
>             Thanks.
> 
>             Le 09/03/2026 à 23:05, Paul A Jungwirth a écrit :
>              > On Mon, Mar 9, 2026 at 1:21 PM Pierre Forstmann
>              > <pierre.forstmann@gmail.com
>             <mailto:pierre.forstmann@gmail.com>> wrote:
>              >> Hello,
>              >>
>              >> I can write a LISTAGG aggregate for:
>              >>
>              >> create table emp(deptno numeric, ename text);
>              >>
>              >> SELECT deptno, LISTAGG(ename, ','::text ORDER BY ename)
>             AS employees
>              >> FROM   emp GROUP BY deptno ORDER BY deptno;
>              >>
>              >> I would like to know if is possible to create an
>             aggregate LISTAGG that
>              >> would work like in Oracle:
>              >>
>              >> SELECT deptno,
>              >>          listagg(ename, ',') WITHIN GROUP (ORDER BY
>             ename) AS employees
>              >> FROM emp
>              >> GROUP BY deptno
>              >> ORDER BY deptno;
>              > I don't think you need a custom aggregate here. In
>             Postgres you can say:
>              >
>              > select deptno,
>              >        string_agg(ename, ',' ORDER BY ename) AS employees
>              > FROM emp
>              > GROUP BY deptno
>              > ORDER BY deptno;
>              >
> 
> 
>