CODEWITHSUNDEEP
sqlpostgresql
user219820
user219820

Reputation: 153

Can you use a CTE in a CASE expression?

I'm using PostgreSQL 14.1.

I tried this query:

with

parent_nodes as
(select p from bst where p is not null)

select n,
case 
when p is null then 'Root'
when n not in parent_nodes then 'Leaf' 
else 'Inner'
end node
from bst;

This fails with this error:

ERROR:  syntax error at or near "parent_nodes"
LINE 9: when n not in parent_nodes then 'Leaf'

If I replace CTE parent_nodes with a subquery i.e.

when n not in (select p from bst where p is not null) then 'Leaf'

it works. Is it not possible to use a CTE in a CASE expression?

Upvotes: 0

Views: 1404

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246383

You can imagine a CTE as being a view that is defined only for a single query. Similar to a view, it is used like a table, that is, in the FROM clause of a (sub)query.

That means that you have to write

... IN (SELECT ... FROM cte)

even if the CTE has only a single column and there would be no ambiguity.

Upvotes: 1

Related Questions