Missing FROM-clause entry for table in postgres CTE

Question

Using the following data:

CREATE TABLE a (
    x int
);
CREATE TABLE b (
    y int
);

INSERT INTO a VALUES 
(1), (2);
INSERT INTO b VALUES 
(1), (1), (2), (2) ;

I'm trying to query as:

WITH a_cte AS (
     SELECT min(x) AS min_a
     FROM a
)
SELECT * FROM b 
WHERE b.y <= a_cte.min_a; 

But am receiving the error:

SQL Error [42P01]: ERROR: missing FROM-clause entry for table "a_cte"

I don't understand the reasoning here, as I thought I'd created the temp table a_cte and could freely use it within the scope.

Note - this is on PostgreSQL 14.2

Answer 1

I don't understand the reasoning here, as I thought I'd created the temp table a_cte and could freely use it within the scope

You are facing with SQL Error [42P01]: ERROR: missing FROM-clause entry for table "a_cte" error because you never select from a_cte.

Use:

WITH a_cte AS (
     SELECT min(x) AS min_a
     FROM a
)
SELECT b.* 
FROM b  
inner join a_cte on b.y <= a_cte.min_a

https://dbfiddle.uk/Ec_MtFek

WITH Queries (Common Table Expressions)

Edit.

I don't understand how this is fundamentally different to my usage though, just has a join instead of a where. Can you write it so that WHERE works please?

As I already mentioned, when using WITH a_cte AS () you need to use a_cte.

The where is redundant on your case , it is used on the join condition.

Maybe you are confusing the old join syntax which I do not recommend. This is still a join , but written differently

WITH a_cte AS (
     SELECT min(x) AS min_a
     FROM a
)
SELECT * FROM b, a_cte
WHERE b.y <= a_cte.min_a; 

https://dbfiddle.uk/uk_98YSX