Feedback-ID: i76984098:Fastmail
Message-ID: <52acd108-2949-4762-80c2-c761662fde73@aklaver.com>
Date: Wed, 25 Feb 2026 08:00:03 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: PostgreSQL query with FULL OUTER JOIN and subquery using column
 of GROUP BY expression ERROR: subquery uses ungrouped column from outer query
To: PALAYRET Jacques <jacques.palayret@meteo.fr>,
 pgsql-general@lists.postgresql.org
References: <970600119.344834366.1772018068349.JavaMail.zimbra@meteo.fr>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <970600119.344834366.1772018068349.JavaMail.zimbra@meteo.fr>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/52acd108-2949-4762-80c2-c761662fde73%40aklaver.com>
Precedence: bulk

On 2/25/26 3:14 AM, PALAYRET Jacques wrote:
> 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?

Questions:

1) Why FROM (SELECT * FROM t1 ) AS t1 instead of just FROM t1?
    Same for JOIN (SELECT * FROM t3   ) t3

2) Why are the field names not table qualified e.g. t2.id, t3.id, etc?

3) What is the desired outcome?
	
> 
> Regards
> ----- Météo-France -----
> PALAYRET Jacques
> DCSC/GDC
> jacques.palayret@meteo.fr
> Fixe : +33 561078319


-- 
Adrian Klaver
adrian.klaver@aklaver.com