PostgreSQL: Using CTE with IN

Learning CTE right now, the following query is super basic and it doesn’t really have any useful value but I don’t understand why it doesn’t work

with cte_actors as (
    select first_name from actor where first_name like 'P%' or first_name like 'E%'
)

select
    first_name
from
    actor
where
    first_name in (cte_actors)

The error I’m getting is “ERROR: column “cte_actors” does not exist”

Currently using postgres 14 and DBeaver

Answer

You need to SELECT from the CTE:

with cte_actors as (
    select first_name 
    from actor 
    where first_name like 'P%' or first_name like 'E%'
)
select first_name
from actor
where first_name in (select first_name from cte_actors)