Why do I get an error querying from column alias?

I'm getting an error querying from the column alias and don't understand why. In the example below, if I run a query from the actual column, no problem. I concatenate first_name and last_name columns into a fullname column alias and then get the output.

SELECT first_name ||' '|| last_name AS fullname
FROM actor;

Output:

Now, if I create a column alias, I get the error. In this example, I'm concatenating first_name and last_name into a fullname column alias, and then query the names between value1 and value2.

SELECT first_name ||' '|| last_name AS fullname
FROM actor;
WHERE fullname BETWEEN 'Zero Cage' AND 'Fred Costner';

Output:

Thanks in advance for your taking the time to help!

In postgres document:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

That's according to the SQL standard and may not be very intuitive. The (historic) reason behind this is the sequence of events in a SELECT query. WHERE and HAVING are resolved before column aliases are considered, while GROUP BY and ORDER BY happen later, after column aliases have been applied. Also note that conflicts between input and output names are resolved differently in ORDER BY and GROUP BY - another historic oddity (with a reason behind it, but potentially confusing nonetheless).

You can use one of the below manners:

1. Use full both column name

SELECT first_name || ' ' || last_name AS fullname
FROM actor
WHERE first_name || ' ' || last_name BETWEEN :conditio1 AND :conditio2

2. Use CTE

WITH data s (
  SELECT first_name || ' ' || last_name AS fullname
  FROM actor
)
SELECT *
FROM data
WHERE fullname BETWEEN :conditio1 AND :conditio2

3. Use subquery

SELECT *
FROM (
  SELECT first_name || ' ' || last_name AS fullname
  FROM actor
) tmp
WHERE tmp.fullname BETWEEN :conditio1 AND :conditio2