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;

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;

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;

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;

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?

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

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;