public inbox for [email protected]help / color / mirror / Atom feed
LISTAGG à la Oracle in PostgreSQL 8+ messages / 6 participants [nested] [flat]
* LISTAGG à la Oracle in PostgreSQL @ 2026-03-09 20:21 Pierre Forstmann <[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 22:05 Paul A Jungwirth <[email protected]> parent: 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-10 08:45 Juan Rodrigo Alejandro Burgos Mella <[email protected]> parent: Pierre Forstmann <[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-10 18:53 Pierre Forstmann <[email protected]> parent: Paul A Jungwirth <[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-10 19:57 Juan Rodrigo Alejandro Burgos Mella <[email protected]> parent: Pierre Forstmann <[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-10 20:15 Isaac Morland <[email protected]> parent: Juan Rodrigo Alejandro Burgos Mella <[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-11 13:55 Peter Eisentraut <[email protected]> parent: Pierre Forstmann <[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
* Re: LISTAGG à la Oracle in PostgreSQL @ 2026-03-11 15:53 [email protected] parent: Isaac Morland <[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
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