Postgresql Does Not Accept Column Alias in Where Clause

Why can't I use my column alias in WHERE clause?

You have several issues with your query:

The filtering conditions should be in the outer query.
The new column definition should be in the inner query.
The order by should be in the outer query.

With these changes, it should work fine:

SELECT ID, NUMBER_OF_PEOPLE, PREV_NUMBER_OF_PEOPLE, DATE
FROM (SELECT D.*,
             LAG(NUMBER_OF_PEOPLE) OVER (ORDER BY DATE) AS PREV_NUMBER_OF_PEOPLE
      FROM DATAFRAME D
     ) AS InnerQuery
WHERE NUMBER_OF_PEOPLE <> PREV_NUMBER_OF_PEOPLE AND
      DATE >= CURRENT_DATE - 90
ORDER BY DATE DESC;

You need the filtering after the LAG() so you can include the earliest day in the date range. If you filter in the inner query, the LAG() will return NULL in that case.

You need to define the alias in the subquery so you can refer to it in the WHERE. Aliases defined in a SELECT cannot be used in the corresponding WHERE. This is a SQL rule, not due to the database you are using.

Column alias is not recognized

Alas, that is true. Column aliases are not allowed. One solution is to repeat the expression:

SELECT EXTRACT(YEAR FROM rental_ts) as year,
       COUNT(DISTINCT rental_id)
FROM rental
GROUP BY year
HAVING EXTRACT(YEAR FROM rental_ts) = 2020;

A better solution is to filter before aggregating:

SELECT EXTRACT(YEAR FROM rental_ts) as year,
       COUNT(DISTINCT rental_id)
FROM rental
WHERE rental_ts >= '2020-01-01' AND rental_ts < '2021-01-01'
GROUP BY year;

This is better for two reasons. First, it is index (and partition) compatible. Second, it reduces the amount of data needed for the aggregation.

not recognizing alias in WHERE clause

The column aliases (here: error_percentage) are not accessible before the construction of the result set. ORDER BY is not affected as sorting happens (necessarily) _after_the result set has been retrieved.

Use

SELECT agg.*
  FROM (
          SELECT date, ((CAST(error AS float) / CAST(success AS float)) *100) AS error_percentage
            FROM daily_report
        ORDER BY error_percentage DESC
       ) agg
 WHERE error_percentage > 1
     ;

It's more efficient and probably cleaner if you trim the result set first:

  SELECT agg.*
    FROM (
            SELECT date, ((CAST(error AS float) / CAST(success AS float)) *100) AS error_percentage
              FROM daily_report
         ) agg
   WHERE error_percentage > 1
ORDER BY error_percentage DESC
     ;

Reference alias in WHERE clause

This is not possible as in sql, the order of execution is first, the where clause and then the select. At the time where clause is getting executed, it does not know what you have defined as an alias and so you will get that error.

You need to rewrite your query like this..

SELECT
    SUBSTRING(pk, 6, 2)::INT AS _year
FROM
    listing
WHERE
SUBSTRING(pk, 6, 2)::INT > 90

Using an Alias column in the where clause in Postgresql

MySQL's support is, as you experienced, non-standard. The correct way is to reprint the same expression used in the SELECT clause:

SELECT
    jobs.*, 
    CASE 
         WHEN lead_informations.state IS NOT NULL THEN lead_informations.state 
         ELSE 'NEW' 
    END AS lead_state
FROM
    jobs
    LEFT JOIN lead_informations ON
        lead_informations.job_id = jobs.id
        AND
        lead_informations.mechanic_id = 3
WHERE
    lead_informations.state IS NULL

Why do I get an error querying from column alias?

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:

Use full both column name

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

Use CTE

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

Use subquery

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

How to use the alias of my calculated parameter (in SELECT clause) in WHERE clause with PostgreSQL to prevent repetition?

You can't reuse a select alias in the where clause. You need to repeat the expression, or use a subquery or cte.

For what it's worth, you can use between to have the expression just once instead of twice in the where clause:

SELECT date_part('year', CURRENT_DATE) - f.birth_year AS age
FROM public.foo f
WHERE date_part('year', CURRENT_DATE) - f.birth_year BETWEEN 20 AND 30

As for the subquery solution, that would be:

SELECT *
FROM (
    SELECT date_part('year', CURRENT_DATE) - f.birth_year AS age
    FROM public.foo
) f
WHERE age BETWEEN 20 AND 30