1

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

2
  • @ypercubeᵀᴹ no - but I thought that the selection was within the scope of the with statement
    – baxx
    Nov 15, 2022 at 14:22
  • 1
    You really don't need a CTE here. WHERE b.y <= (SELECT MIN(x) FROM a) is perfectly valid unless you're getting paid by the line.
    – user212533
    Nov 15, 2022 at 17:01

1 Answer 1

4

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

5
  • thanks - 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?
    – baxx
    Nov 15, 2022 at 14:21
  • seems I would need where b.y <= (select min_a from a_cte), which works, maybe I just need to read the docs more - I'm not too sure why i need to do this for a where but not a join though
    – baxx
    Nov 15, 2022 at 14:23
  • 1
    @baxx check the last Edit part Nov 15, 2022 at 14:37
  • It doesn't work for the same reason that SELECT * FROM b WHERE b.y = a.y doesn't work, a table is not in any FROM clause. Nov 15, 2022 at 14:42
  • @ErgestBasha thanks, i was using where (rather than join) as in the actual data i was filtering on inequalities (time comparison) rather than exact matches, but that's not represented in the data I gave so clearly (other than the use of <=)
    – baxx
    Nov 15, 2022 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.