Date: Wed, 25 Feb 2026 11:14:28 +0000 (GMT)
From: PALAYRET Jacques <jacques.palayret@meteo.fr>
To: pgsql-general@lists.postgresql.org
Message-ID: <970600119.344834366.1772018068349.JavaMail.zimbra@meteo.fr>
Subject: 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="=_421e0b7a-4bb5-4e9d-868c-c8b2862dfabe"
Thread-Index: 8hulqi8uEdkVvJbvpLI0ua4YNfyGJw==
Thread-Topic: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
Archived-At: <https://www.postgresql.org/message-id/970600119.344834366.1772018068349.JavaMail.zimbra%40meteo.fr>
Precedence: bulk

--=_421e0b7a-4bb5-4e9d-868c-c8b2862dfabe
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

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

--=_421e0b7a-4bb5-4e9d-868c-c8b2862dfabe
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><body><div id=3D"zimbraEditorContainer" style=3D"font-family: garamon=
d, new york, times, serif; font-size: 12pt; color: #000000" class=3D"12"><d=
iv> <!--StartFragment-->Hello,<br><br>I have <span style=3D"text-decoration=
: underline;" data-mce-style=3D"text-decoration: underline;">3 tables</span=
> : t1, t2 and t3<br>t1(posx integer, posy integer, dat timestamp, val_grid=
 numeric) <br>&nbsp; with Primary Key=3D(posx, posy, dat)<br>t2(id integer,=
 x integer, y integer)<br>&nbsp; with Primary Key=3D(id)<br>t3(id integer, =
dat timestamp, c1 numeric)<br>&nbsp; with Primary Key=3D(id, dat)<br><br><s=
pan style=3D"text-decoration: underline;" data-mce-style=3D"text-decoration=
: underline;">My (very simplified) query&nbsp; that doesn't work</span>:<br=
>SELECT </div><div>&nbsp; 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::sma=
llint,<br>avg(val_grid), sum(c1),<br></div><div>&nbsp; <span style=3D"backg=
round-color: rgb(255, 255, 153);" data-mce-style=3D"background-color: #ffff=
99;"><strong>(SELECT col1 FROM another_table WHERE another_table.np=3Did LI=
MIT 1)</strong></span><br>FROM<br>&nbsp; (SELECT * FROM t1 ) AS t1</div><di=
v>&nbsp; <strong>JOIN</strong> t2&nbsp; ON (t1.posx=3Dt2.x AND t1.posy=3Dt2=
.y)</div><div>&nbsp; <span style=3D"background-color: rgb(255, 255, 153);" =
data-mce-style=3D"background-color: #ffff99;"><strong>FULL OUTER JOIN</stro=
ng></span> (SELECT * FROM t3&nbsp;&nbsp; ) t3 USING(id, dat)<br>--WHERE ...=
<br><strong>GROUP BY</strong> id, to_char(dat, 'YYYYMM'), CASE FLOOR((TO_NU=
MBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END:=
:smallint<br>;<br><span style=3D"background-color: rgb(255, 255, 153);" dat=
a-mce-style=3D"background-color: #ffff99;">ERROR:&nbsp; subquery uses ungro=
uped column "t2.id" from outer query</span><br><br>-&gt; The problem is on =
the third line (the subquery) :<br>(SELECT col1 FROM another_table WHERE an=
other_table.np=3Did LIMIT 1)<br><br>However, If I replace "<span style=3D"b=
ackground-color: rgb(255, 255, 153);" data-mce-style=3D"background-color: #=
ffff99;">FULL OUTER</span>" by "<strong>LEFT OUTER</strong>" or by "<strong=
>RIGHT OUTER</strong>", <strong>it works</strong>.<br><br>Could you please =
tell me how to correct the query, to fix this problem?<br><br>Regards<!--En=
dFragment--> </div><div data-marker=3D"__SIG_PRE__">----- M=C3=A9t=C3=A9o-F=
rance -----<br>PALAYRET Jacques<br>DCSC/GDC<br>jacques.palayret@meteo.fr<br=
>Fixe&nbsp;:&nbsp;+33 561078319</div></div></body></html>
--=_421e0b7a-4bb5-4e9d-868c-c8b2862dfabe--