Using ROW_NUMBER() Function in a WHERE statement in Postgres

Question

The following works in Postgres:

SELECT
    *
FROM
    (
        SELECT
            product_id,
            product_name,
            price,
            ROW_NUMBER () OVER (ORDER BY product_name)
        FROM
            products
    ) x
WHERE
    ROW_NUMBER BETWEEN 6 AND 10;

My question is why does the following simpler attempt not work?

        SELECT
            product_id,
            product_name,
            price,
            ROW_NUMBER () OVER (ORDER BY product_name)
        FROM
            products
     
WHERE
    ROW_NUMBER BETWEEN 6 AND 10;

Answer 1

Because window functions, like row_number(), are applied to the result of the query (in your case that's the inner one), i.e. after the WHERE clause (not statement) has already taken effect.