public inbox for [email protected]
help / color / mirror / Atom feedLISTAGG à la Oracle in PostgreSQL
8+ messages / 6 participants
[nested] [flat]
* LISTAGG à la Oracle in PostgreSQL
@ 2026-03-09 20:21 Pierre Forstmann <[email protected]>
2026-03-09 22:05 ` Re: LISTAGG à la Oracle in PostgreSQL Paul A Jungwirth <[email protected]>
2026-03-10 08:45 ` Re: LISTAGG à la Oracle in PostgreSQL Juan Rodrigo Alejandro Burgos Mella <[email protected]>
2026-03-11 13:55 ` Re: LISTAGG à la Oracle in PostgreSQL Peter Eisentraut <[email protected]>
0 siblings, 3 replies; 8+ messages in thread
From: Pierre Forstmann @ 2026-03-09 20:21 UTC (permalink / raw)
To: [email protected] <[email protected]>
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 ?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: LISTAGG à la Oracle in PostgreSQL
2026-03-09 20:21 LISTAGG à la Oracle in PostgreSQL Pierre Forstmann <[email protected]>
@ 2026-03-09 22:05 ` Paul A Jungwirth <[email protected]>
2026-03-10 18:53 ` Re: LISTAGG à la Oracle in PostgreSQL Pierre Forstmann <[email protected]>
2 siblings, 1 reply; 8+ messages in thread
From: Paul A Jungwirth @ 2026-03-09 22:05 UTC (permalink / raw)
To: Pierre Forstmann <[email protected]>; +Cc: [email protected] <[email protected]>
On Mon, Mar 9, 2026 at 1:21 PM Pierre Forstmann
<[email protected]> 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 ~{:-)
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: LISTAGG à la Oracle in PostgreSQL
2026-03-09 20:21 LISTAGG à la Oracle in PostgreSQL Pierre Forstmann <[email protected]>
2026-03-09 22:05 ` Re: LISTAGG à la Oracle in PostgreSQL Paul A Jungwirth <[email protected]>
@ 2026-03-10 18:53 ` Pierre Forstmann <[email protected]>
2026-03-10 19:57 ` Re: LISTAGG à la Oracle in PostgreSQL Juan Rodrigo Alejandro Burgos Mella <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Pierre Forstmann @ 2026-03-10 18:53 UTC (permalink / raw)
To: Paul A Jungwirth <[email protected]>; +Cc: [email protected] <[email protected]>
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
> <[email protected]> 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;
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: LISTAGG à la Oracle in PostgreSQL
2026-03-09 20:21 LISTAGG à la Oracle in PostgreSQL Pierre Forstmann <[email protected]>
2026-03-09 22:05 ` Re: LISTAGG à la Oracle in PostgreSQL Paul A Jungwirth <[email protected]>
2026-03-10 18:53 ` Re: LISTAGG à la Oracle in PostgreSQL Pierre Forstmann <[email protected]>
@ 2026-03-10 19:57 ` Juan Rodrigo Alejandro Burgos Mella <[email protected]>
2026-03-10 20:15 ` Re: LISTAGG à la Oracle in PostgreSQL Isaac Morland <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Juan Rodrigo Alejandro Burgos Mella @ 2026-03-10 19:57 UTC (permalink / raw)
To: Pierre Forstmann <[email protected]>; +Cc: Paul A Jungwirth <[email protected]>; [email protected] <[email protected]>
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 (<
[email protected]>) 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
> > <[email protected]> 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;
> >
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: LISTAGG à la Oracle in PostgreSQL
2026-03-09 20:21 LISTAGG à la Oracle in PostgreSQL Pierre Forstmann <[email protected]>
2026-03-09 22:05 ` Re: LISTAGG à la Oracle in PostgreSQL Paul A Jungwirth <[email protected]>
2026-03-10 18:53 ` Re: LISTAGG à la Oracle in PostgreSQL Pierre Forstmann <[email protected]>
2026-03-10 19:57 ` Re: LISTAGG à la Oracle in PostgreSQL Juan Rodrigo Alejandro Burgos Mella <[email protected]>
@ 2026-03-10 20:15 ` Isaac Morland <[email protected]>
2026-03-11 15:53 ` Re: LISTAGG à la Oracle in PostgreSQL [email protected]
0 siblings, 1 reply; 8+ messages in thread
From: Isaac Morland @ 2026-03-10 20:15 UTC (permalink / raw)
To: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; +Cc: Pierre Forstmann <[email protected]>; Paul A Jungwirth <[email protected]>; [email protected] <[email protected]>
On Tue, 10 Mar 2026 at 15:57, Juan Rodrigo Alejandro Burgos Mella <
[email protected]> 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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: LISTAGG à la Oracle in PostgreSQL
2026-03-09 20:21 LISTAGG à la Oracle in PostgreSQL Pierre Forstmann <[email protected]>
2026-03-09 22:05 ` Re: LISTAGG à la Oracle in PostgreSQL Paul A Jungwirth <[email protected]>
2026-03-10 18:53 ` Re: LISTAGG à la Oracle in PostgreSQL Pierre Forstmann <[email protected]>
2026-03-10 19:57 ` Re: LISTAGG à la Oracle in PostgreSQL Juan Rodrigo Alejandro Burgos Mella <[email protected]>
2026-03-10 20:15 ` Re: LISTAGG à la Oracle in PostgreSQL Isaac Morland <[email protected]>
@ 2026-03-11 15:53 ` [email protected]
0 siblings, 0 replies; 8+ messages in thread
From: [email protected] @ 2026-03-11 15:53 UTC (permalink / raw)
To: Isaac Morland <[email protected]>; Juan Rodrigo Alejandro Burgos Mella <[email protected]>; Paul A Jungwirth <[email protected]>; [email protected] <[email protected]>
Actually I'm trying to do for IvorySQL.
On 10/03/2026 21:15, Isaac Morland <[email protected]> wrote:
> On Tue, 10 Mar 2026 at 15:57, Juan Rodrigo Alejandro Burgos Mella
> <[email protected] <mailto:[email protected]>> 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?
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: LISTAGG à la Oracle in PostgreSQL
2026-03-09 20:21 LISTAGG à la Oracle in PostgreSQL Pierre Forstmann <[email protected]>
@ 2026-03-10 08:45 ` Juan Rodrigo Alejandro Burgos Mella <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Juan Rodrigo Alejandro Burgos Mella @ 2026-03-10 08:45 UTC (permalink / raw)
To: Pierre Forstmann <[email protected]>; +Cc: [email protected] <[email protected]>
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 (<
[email protected]>) 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 ?
>
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: LISTAGG à la Oracle in PostgreSQL
2026-03-09 20:21 LISTAGG à la Oracle in PostgreSQL Pierre Forstmann <[email protected]>
@ 2026-03-11 13:55 ` Peter Eisentraut <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Peter Eisentraut @ 2026-03-11 13:55 UTC (permalink / raw)
To: Pierre Forstmann <[email protected]>; [email protected] <[email protected]>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-03-11 15:53 UTC | newest]
Thread overview: 8+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-09 20:21 LISTAGG à la Oracle in PostgreSQL Pierre Forstmann <[email protected]>
2026-03-09 22:05 ` Paul A Jungwirth <[email protected]>
2026-03-10 18:53 ` Pierre Forstmann <[email protected]>
2026-03-10 19:57 ` Juan Rodrigo Alejandro Burgos Mella <[email protected]>
2026-03-10 20:15 ` Isaac Morland <[email protected]>
2026-03-11 15:53 ` [email protected]
2026-03-10 08:45 ` Juan Rodrigo Alejandro Burgos Mella <[email protected]>
2026-03-11 13:55 ` Peter Eisentraut <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox