postgres.email
❮

LISTAGG à la Oracle in PostgreSQL

  • Jump to comment-1
    Pierre Forstmann<pierre.forstmann@gmail.com>
    Mar 9, 2026, 8:21 PM UTC
    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 ?
    View in PostgreSQL Archives →
    • Jump to comment-1
      Paul A Jungwirth<pj@illuminatedcomputing.com>
      Mar 9, 2026, 10:05 PM UTC
      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
      View in PostgreSQL Archives →
      • Jump to comment-1
        Pierre Forstmann<pierre.forstmann@gmail.com>
        Mar 10, 2026, 6:53 PM UTC
        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;
        View in PostgreSQL Archives →
        • Jump to comment-1
          Juan Rodrigo Alejandro Burgos Mella<rodrigoburgosmella@gmail.com>
          Mar 10, 2026, 7:57 PM UTC
          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;


          View in PostgreSQL Archives →
          • Jump to comment-1
            Isaac Morland<isaac.morland@gmail.com>
            Mar 10, 2026, 8:15 PM UTC
            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?
            View in PostgreSQL Archives →
            • Jump to comment-1
              pierre.forstmann@gmail.com
              Mar 11, 2026, 3:53 PM UTC
              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?
              View in PostgreSQL Archives →
          • Jump to comment-1
            Pavel Stehule<pavel.stehule@gmail.com>
            Mar 10, 2026, 8:23 PM UTC
            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;


            View in PostgreSQL Archives →
            • Jump to comment-1
              Pavel Stehule<pavel.stehule@gmail.com>
              Mar 10, 2026, 9:47 PM UTC
              ú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;


              View in PostgreSQL Archives →
              • Jump to comment-1
                pierre.forstmann@gmail.com
                Mar 11, 2026, 3:53 PM UTC
                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;
                View in PostgreSQL Archives →
    • Jump to comment-1
      Juan Rodrigo Alejandro Burgos Mella<rodrigoburgosmella@gmail.com>
      Mar 10, 2026, 8:45 AM UTC
      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 ?






      View in PostgreSQL Archives →
    • Jump to comment-1
      Peter Eisentraut<peter@eisentraut.org>
      Mar 11, 2026, 1:56 PM UTC
      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.
      View in PostgreSQL Archives →