Request a Demo
Home   >   mailing lists

Re: LISTAGG à la Oracle in PostgreSQL - Mailing list pgsql-general

From Juan Rodrigo Alejandro Burgos Mella
Subject Re: LISTAGG à la Oracle in PostgreSQL
Date
Msg-id CAHbZ42wBt2-C6i3520QDbURpiTooD5AWmK6s3qkFnzFPJxSZLg@mail.gmail.com
Whole thread
In response to LISTAGG à la Oracle in PostgreSQL  (Pierre Forstmann <pierre.forstmann@gmail.com>)
List pgsql-general
Tree view
Pierre
The equivalent in PostgreSQL is through:

SELECT deptno,       STRING_AGG(ename, ',' ORDER BY ename) AS employees
FROM emp
GROUP BY deptno
ORDER 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 ?






pgsql-general by date:

Previous
From: Paul A Jungwirth
Date:
Subject: Re: LISTAGG à la Oracle in PostgreSQL
Next
From: Greg Sabino Mullane
Date:
Subject: Re: Index (primary key) corrupt?