Horton
Menu

Thread

  1. LISTAGG à la Oracle in PostgreSQL

    pierre.forstmann@gmail.com — 2026-03-09T20:21:44Z

    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 ?

  2. Re: LISTAGG à la Oracle in PostgreSQL

    Paul A Jungwirth <pj@illuminatedcomputing.com> — 2026-03-09T22:05:17Z

    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

  3. Re: LISTAGG à la Oracle in PostgreSQL

    Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com> — 2026-03-10T08:45:27Z

    Pierre
The equivalent in PostgreSQL is through:

SELECT deptno,
       STRING_AGG(ename, ',' ORDER BY ename) AS employeesFROM empGROUP
BY deptnoORDER BY deptno;


Atte
JRBM

El lun, 9 mar 2026 a las 15:21, Pierre Forstmann (<
pierre.forstmann@gmail.com>) escribió:

> 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 ?
>
>
>
>
>
>
>

  4. Re: LISTAGG à la Oracle in PostgreSQL

    pierre.forstmann@gmail.com — 2026-03-10T18:53:30Z

    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;
>

  5. Re: LISTAGG à la Oracle in PostgreSQL

    Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com> — 2026-03-10T19:57:39Z

    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.

Atte
JRBM

El mar, 10 mar 2026 a las 13:53, Pierre Forstmann (<
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> 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;
> >
>
>
>

  6. Re: LISTAGG à la Oracle in PostgreSQL

    Isaac Morland <isaac.morland@gmail.com> — 2026-03-10T20:15:02Z

    On Tue, 10 Mar 2026 at 15:57, Juan Rodrigo Alejandro Burgos Mella <
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?

  7. Re: LISTAGG à la Oracle in PostgreSQL

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-10T20:23:14Z

    Hi

út 10. 3. 2026 v 20:58 odesílatel Juan Rodrigo Alejandro Burgos Mella <
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

Regards

Pavel


>
> Atte
> JRBM
>
> El mar, 10 mar 2026 a las 13:53, Pierre Forstmann (<
> 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> 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;
>> >
>>
>>
>>

  8. Re: LISTAGG à la Oracle in PostgreSQL

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-10T21:46:59Z

    út 10. 3. 2026 v 21:23 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

> Hi
>
> út 10. 3. 2026 v 20:58 odesílatel Juan Rodrigo Alejandro Burgos Mella <
> 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
>

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>) 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> 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;
>>> >
>>>
>>>
>>>

  9. Re: LISTAGG à la Oracle in PostgreSQL

    Peter Eisentraut <peter@eisentraut.org> — 2026-03-11T13:55:53Z

    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.

  10. Re: LISTAGG à la Oracle in PostgreSQL

    pierre.forstmann@gmail.com — 2026-03-11T15:53:02Z

    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?
>

  11. Re: LISTAGG à la Oracle in PostgreSQL

    pierre.forstmann@gmail.com — 2026-03-11T15:53:52Z

    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;
>              >
> 
> 
>