PostgreSQL query - AVG() function outside of temporary table

Question

I currently have code along these lines:

SELECT num
  FROM (
        SELECT ... Code that returns the table I would expect ...
  ) table_name
WHERE num > (SELECT AVG(num) FROM table_name);

Currently the query will pull an error: ERROR: relation "table_name" does not exist.

Why would this be happening?

As I said in the code, I can copy the select statement from inside the bracers:

SELECT ... Code that returns the table I would expect ...

and it will return a table that is as I expect and it contains a column called 'num'.

As a side note, what is it called in SQL when I give a table a name (table_name in this case)? A temporary table as I called it in the title? It is very hard to search for solutions to this problem without knowing what it is called.

Thanks, Cameron

Answer 1

One solution to fix your problem is to use ctes.

with table_name as
(SELECT ... Code that returns the table I would expect ...)
,avg_num as (select avg(num) as avgnum from table_name)
select t.num 
from table_name t join avg_num a
on t.num > a.avgnum;