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 1vvHJT-00BhAX-1b for pgsql-general@arkaria.postgresql.org; Wed, 25 Feb 2026 16:00:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vvHJS-00755i-1W for pgsql-general@arkaria.postgresql.org; Wed, 25 Feb 2026 16:00:34 +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 1vvHJB-0070tN-1d for pgsql-general@lists.postgresql.org; Wed, 25 Feb 2026 16:00:17 +0000 Received: from zcsmtaf02-pub.meteo.fr ([137.129.63.6]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1vvHJ7-000000018mr-1ImU for pgsql-general@lists.postgresql.org; Wed, 25 Feb 2026 16:00:16 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id 1B96F40ED6DC for ; Wed, 25 Feb 2026 16:00:12 +0000 (GMT) Received: from zcsmtaf02-pub.meteo.fr ([127.0.0.1]) by localhost (zcsmtaf02.meteo.fr [127.0.0.1]) (amavis, port 10032) with ESMTP id hzrCXdJz2fCX for ; Wed, 25 Feb 2026 16:00:12 +0000 (GMT) Received: from localhost (localhost.localdomain [127.0.0.1]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id EEC2340ED6E8 for ; Wed, 25 Feb 2026 16:00:11 +0000 (GMT) X-Virus-Scanned: amavis at meteo.fr Received: from zcsmtaf02-pub.meteo.fr ([127.0.0.1]) by localhost (zcsmtaf02.meteo.fr [127.0.0.1]) (amavis, port 10026) with ESMTP id y3muKFQS1FEb for ; Wed, 25 Feb 2026 16:00:11 +0000 (GMT) Received: from zcsmsm04.meteo.fr (zcsmsm04.meteo.fr [172.24.3.124]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id BE94440ED6DC for ; Wed, 25 Feb 2026 16:00:11 +0000 (GMT) Date: Wed, 25 Feb 2026 16:00:11 +0000 (GMT) From: PALAYRET Jacques To: pgsql-general@lists.postgresql.org Message-ID: <964199985.345244682.1772035211240.JavaMail.zimbra@meteo.fr> In-Reply-To: <970600119.344834366.1772018068349.JavaMail.zimbra@meteo.fr> References: <970600119.344834366.1772018068349.JavaMail.zimbra@meteo.fr> Subject: Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_1a34cb9e-e1e9-4f66-bade-6d20291cb571" X-Originating-IP: [172.24.2.157] X-Mailer: Zimbra 9.0.0_GA_4583 (ZimbraWebClient - FF128 (Win)/9.0.0_GA_4583) Thread-Topic: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query Thread-Index: 8hulqi8uEdkVvJbvpLI0ua4YNfyGJ6/q4dCA List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_1a34cb9e-e1e9-4f66-bade-6d20291cb571 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable hello again,=20 It seems that following can be a solution (adding sub-query " SELECT * FROM= " after the main FROM clause) :=20 SELECT=20 id, to_char(dat, 'YYYYMM'), CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1= )/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint,=20 avg(val_grid), sum(c1),=20 (SELECT col1 FROM another_table WHERE another_table.np=3Did LIMIT 1)=20 FROM (=20 SELECT * FROM (=20 (SELECT * FROM t1 ) AS t1=20 JOIN t2 ON (t1.posx=3Dt2.x AND t1.posy=3Dt2.y)=20 FULL OUTER JOIN (SELECT * FROM t3 ) t3 USING(id, dat)=20 ) tb=20 ) t=20 --WHERE ...=20 GROUP BY id, to_char(dat, 'YYYYMM'), CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'= ),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint=20 ;=20 Regards=20 De: "PALAYRET Jacques" =20 =C3=80: pgsql-general@lists.postgresql.org=20 Envoy=C3=A9: Mercredi 25 F=C3=A9vrier 2026 12:14:28=20 Objet: PostgreSQL query with FULL OUTER JOIN and subquery using column of G= ROUP BY expression ERROR: subquery uses ungrouped column from outer query= =20 Hello,=20 I have 3 tables : t1, t2 and t3=20 t1(posx integer, posy integer, dat timestamp, val_grid numeric)=20 with Primary Key=3D(posx, posy, dat)=20 t2(id integer, x integer, y integer)=20 with Primary Key=3D(id)=20 t3(id integer, dat timestamp, c1 numeric)=20 with Primary Key=3D(id, dat)=20 My (very simplified) query that doesn't work :=20 SELECT=20 id, to_char(dat, 'YYYYMM'), CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1= )/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint,=20 avg(val_grid), sum(c1),=20 (SELECT col1 FROM another_table WHERE another_table.np=3Did LIMIT 1)=20 FROM=20 (SELECT * FROM t1 ) AS t1=20 JOIN t2 ON (t1.posx=3Dt2.x AND t1.posy=3Dt2.y)=20 FULL OUTER JOIN (SELECT * FROM t3 ) t3 USING(id, dat)=20 --WHERE ...=20 GROUP BY id, to_char(dat, 'YYYYMM'), CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'= ),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint=20 ;=20 ERROR: subquery uses ungrouped column "t2.id" from outer query=20 -> The problem is on the third line (the subquery) :=20 (SELECT col1 FROM another_table WHERE another_table.np=3Did LIMIT 1)=20 However, If I replace " FULL OUTER " by " LEFT OUTER " or by " RIGHT OUTER = ", it works .=20 Could you please tell me how to correct the query, to fix this problem?=20 Regards=20 ----- M=C3=A9t=C3=A9o-France -----=20 PALAYRET Jacques=20 DCSC/GDC=20 jacques.palayret@meteo.fr=20 Fixe : +33 561078319=20 --=_1a34cb9e-e1e9-4f66-bade-6d20291cb571 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
hello again,

It seems that following can be= a solution (adding sub-query " SELECT * FROM " after the main FROM clause)=   :
SELECT
  id, to_char(dat, 'YYYYMM'), CASE FLOOR(= (TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE = 3 END::smallint,
avg(val_grid), sum(c1),
  (SELECT col1 F= ROM another_table WHERE another_table.np=3Did LIMIT 1)
FROM (
= SELECT * FROM (
  (SELECT * FROM t1 ) AS t1
&nbs= p; JOIN t2  ON (t1.posx=3Dt2.x AND t1.posy=3Dt2.y)
  FULL OUTE= R JOIN (SELECT * FROM t3   ) t3 USING(id, dat)
) tb
= ) t
--WHERE ...
GROUP BY id, to_char(dat, 'YYYYMM'), = CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 T= HEN 2 ELSE 3 END::smallint
;

Regards
De: "PALAYRET Jacques" <jacques.palayret@m= eteo.fr>
=C3=80: pgsql-general@lists.postgresql.org
Envo= y=C3=A9: Mercredi 25 F=C3=A9vrier 2026 12:14:28
Objet: Postgr= eSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expre= ssion ERROR:  subquery uses ungrouped column from outer query

Hello,

I have 3 tables : t1, t2 and t3
t1(= posx integer, posy integer, dat timestamp, val_grid numeric)
  wit= h Primary Key=3D(posx, posy, dat)
t2(id integer, x integer, y integer)  with Primary Key=3D(id)
t3(id integer, dat timestamp, c1 numeri= c)
  with Primary Key=3D(id, dat)

My (very simplified) query  that doesn't work:<= br>SELECT
  id, to_char(dat, 'YYYYMM'), CASE FLOOR((TO_NUMB= ER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::s= mallint,
avg(val_grid), sum(c1),
  (SELECT col1 FROM another_tab= le WHERE another_table.np=3Did LIMIT 1)
FROM
  (= SELECT * FROM t1 ) AS t1
  JOIN t2  ON= (t1.posx=3Dt2.x AND t1.posy=3Dt2.y)
  FULL OUTER JOIN = (SELECT * FROM t3   ) t3 USING(id, dat)
--WHERE ...
GROUP BY id, to_char(dat, 'YYYYMM'), CASE FLOOR((TO_NUMBER(TO_CHA= R(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint;
ERROR: = subquery uses ungrouped column "t2.id" from outer query

->= ; The problem is on the third line (the subquery) :
(SELECT col1 FROM an= other_table WHERE another_table.np=3Did LIMIT 1)

However, If I repla= ce "FULL OUTER" by "LEFT OUTER" or by "RIGHT OUTER", = it works.

Could you please tell me how to correct t= he query, to fix this problem?

Regards
----- M=C3=A9t=C3= =A9o-France -----
PALAYRET Jacques
DCSC/GDC
jacques.palayret@meteo= .fr
Fixe : +33 561078319

--=_1a34cb9e-e1e9-4f66-bade-6d20291cb571--