Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vzsio-001PIQ-0y for pgsql-general@arkaria.postgresql.org; Tue, 10 Mar 2026 08:45:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vzsik-002FiS-27 for pgsql-general@arkaria.postgresql.org; Tue, 10 Mar 2026 08:45:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vzsik-002FiI-0d for pgsql-general@lists.postgresql.org; Tue, 10 Mar 2026 08:45:42 +0000 Received: from mail-ed1-x52a.google.com ([2a00:1450:4864:20::52a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vzsii-00000001QK3-3tRg for pgsql-general@lists.postgresql.org; Tue, 10 Mar 2026 08:45:41 +0000 Received: by mail-ed1-x52a.google.com with SMTP id 4fb4d7f45d1cf-66281b1018bso3018876a12.2 for ; Tue, 10 Mar 2026 01:45:40 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1773132339; cv=none; d=google.com; s=arc-20240605; b=T2yITs0Xi5XYkPVsqwldaw8dtzAf+IgXKIK4MpGP+y9DNp8rQFBRiiwVhMC1RmCluO IiBnDB2gg+1N1qBZwYdWxd77vVB+1R0cNqnGe8RN73BYQ5vPNroZiZQ/jE8WMO+1m+me q/7hfsIQKGH3qUgiU+uO08HMgRNsVYAwD83XF/OWp3ant0rKPYva8iBUYTLbPO78K2mH RA0FBaBg7avXj7O/7LRp5B674TvN9Lf/GatZI8rinopE6OI/5lkiL6GzPAjriummJW4F Kmsr4RL95uvvCVsnbTHmYGCtCLlrgSpFbrqnjB1QeA+xXzvtfDkd2tUazgUHraO/hVr2 R42w== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=u2+zuxdsnxLr9Ubs+1JHT256LFR+o5z5APvG1EEX0JU=; fh=GYvKIk98ibur9RMlCPnh1wUCkYVMcQyiJc/iuLZ5qNo=; b=HthI7CxP1wUO29eOWq/yi4zsBT9W+c0MTUsBhry6k7Kyx82th/huz9i/mxfigoOjyh J5wTWtdkCYmf62Za+j0gNsKcsHR4KrZW/30O0QVE/EqET8wVHRDLtga+cU/kxiCHcyJA FC62SYuZygJBMUoiQRqEJF2Z5TbaYKrWtXqVRrfOamBIcWPyE2WuyvJ/4JLcYw5rO4Jp 12TYLrreKlSm6aSoJeC1z8BwQFcDIDseEf/i/DmeWqUOH0LfjvfoFfFzgL9mY5eKlzCa CvZUQFj0vG57p4H/44Ut4BfQBIvj0GjOdJ8fqwNt0HBaQ8Xg1F2O03UPMhuInZauvgd2 4b+g==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1773132339; x=1773737139; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=u2+zuxdsnxLr9Ubs+1JHT256LFR+o5z5APvG1EEX0JU=; b=CGikla6d9xA943cKPYykXcWO1Ia5nY2SPowo6RFaz2JdOrhDL0G5x2c8n2Cte///l3 tyO3opQCO3qvQZ52GePI52s8JYv2dMwZjfSpi9puFdq6kb7i3mRLQQW0bV0/YuRP+yJt 3DifKJ0K0KKGkusEHVrLMXWleMhFAX+8SZJC5GvQHsR3/y4cVU/eQUHN8tlzYJKVORiT 47TmVxxLFCe9OevHCmvR8T9m/1TJQZRBNRRa+I80kqtWLuLWqgNE3lfo98/yi7Iey/3j AYdkMsr1Tph6+4vNFZprWDnbuxHa4Oxf2F68PaB0V6hGwCbZIIQKKjDaCExWsR660vcb FsHA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1773132339; x=1773737139; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=u2+zuxdsnxLr9Ubs+1JHT256LFR+o5z5APvG1EEX0JU=; b=UbcWxtg9e6roAWVDI3HMS07A7K5W765h1hn7t1Yp32JqJFqYlD4TMK2zkUrOaQVmHy tP8ZOm9QLeueCtNe2FwsJFbeSYbyyrUbjzctRWOX+HtlW0yq9UYH9u6Uw+NZHRZNCOMO /0i+j1hpd5u+urzI5kD4+XikgEDxK2+mxvn81oHA6QJM+2Q3keoOWZ889/mLOHHIW53U x4c7AB0oFAnyy9Tl1Ta23rvOjqU06eD6hi65HyEdeB1x4N7Tq4Au2qIw4DfxpbTPb3Fw vT0qGqB11UjeHZUyar38HZ7IJEy7YzEY4RwkrE25iWkhFsuNKNpSd09NxQ6tzHs/xwe9 /x8Q== X-Gm-Message-State: AOJu0YyKoC6Cm+oR6JT//zy7qTMQ1MC2j5crfe2Jcl4SluSk9adOiVLO ig/KWhq/bFzE13B3ZIiiNpdIiMxQsixWPmSsSACfQ13LVTC6KtHyxcvgFbIUDI4FGPGKpcoDGdE D9MdWxpT+iwJLJmRFzwDivkdEIirplA== X-Gm-Gg: ATEYQzzHZ+VH6R9YikjDvjbllQDGRUnKRl0xZYYwIxFLY9U/PjdzyBoWah3ZIjqwEvh Zc3rmvFLYNkxPcWPJaivYZqzGtOlYy7xgDYyr0eHz3I4JHJ2cMOlwJcd3wdY9ES///i8D34SgCk zaayNVY/1F5wFsC8ddROO3U5KAWYd49+gft/yQ2MI8qDF6aGeeVKiLjrnGIAg4Vg9xGH1Us1EtY ekAACwGPABA5ab6Gp/a1GZwz6m0Y4Yzg1Yf8egekfrTslqKKrq4OM54L6n0UjaYQowDUnIRA4zD mxbXAhHFQ8ptug== X-Received: by 2002:a17:907:96ac:b0:b8f:9fe5:fd62 with SMTP id a640c23a62f3a-b942e027afcmr719517966b.42.1773132339305; Tue, 10 Mar 2026 01:45:39 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Juan Rodrigo Alejandro Burgos Mella Date: Tue, 10 Mar 2026 03:45:27 -0500 X-Gm-Features: AaiRm51V9kIPCHknCPz7aJdIIDVNSsZPU2nobteH9NxwHtnyQHLXi9kt2vZpEsk Message-ID: Subject: =?UTF-8?Q?Re=3A_LISTAGG_=C3=A0_la_Oracle_in_PostgreSQL?= To: Pierre Forstmann Cc: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000395e75064ca78bf6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000395e75064ca78bf6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=C3=B3: > 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 ? > > > > > > > --000000000000395e75064ca78bf6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Pierre
The equivalent in PostgreSQL is through:
<= div>
<=
span class=3D"gmail-token gmail-keyword" style=3D"color:rgb(166,38,164)">SE=
LECT deptno,
       STRING_AGG(ename, ',' ORDER BY ename) AS employees
F=
ROM emp
G=
ROUP BY deptno
O=
RDER BY deptno;

Atte
JRBM

El lun, 9 mar 2026 a las 15:21, Pie= rre Forstmann (<pierre.for= stmann@gmail.com>) escribi=C3=B3:
Hello,

I can write a LISTAGG aggregate for:

create table emp(deptno numeric, ename text);

SELECT deptno, LISTAGG(ename, ','::text ORDER BY ename) AS employee= s
FROM=C2=A0 =C2=A0emp 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,
=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0listagg(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 ?






--000000000000395e75064ca78bf6--