public inbox for [email protected]
help / color / mirror / Atom feedFrom: PALAYRET Jacques <[email protected]>
To: [email protected]
Subject: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
Date: Wed, 25 Feb 2026 11:14:28 +0000 (GMT)
Message-ID: <[email protected]> (raw)
Hello,
I have 3 tables : t1, t2 and t3
t1(posx integer, posy integer, dat timestamp, val_grid numeric)
with Primary Key=(posx, posy, dat)
t2(id integer, x integer, y integer)
with Primary Key=(id)
t3(id integer, dat timestamp, c1 numeric)
with Primary Key=(id, dat)
My (very simplified) query that doesn't work :
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 FROM another_table WHERE another_table.np=id LIMIT 1)
FROM
(SELECT * FROM t1 ) AS t1
JOIN t2 ON (t1.posx=t2.x AND t1.posy=t2.y)
FULL OUTER JOIN (SELECT * FROM t3 ) t3 USING(id, dat)
--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 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 another_table WHERE another_table.np=id LIMIT 1)
However, If I replace " FULL OUTER " by " LEFT OUTER " or by " RIGHT OUTER ", it works .
Could you please tell me how to correct the query, to fix this problem?
Regards
----- Météo-France -----
PALAYRET Jacques
DCSC/GDC
[email protected]
Fixe : +33 561078319
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox