Postgresql – Column doesn’t exist when using CASE expression in WHERE clause

postgresql

I have this SQL

SELECT 
    price_amount, 
    price_currency, 
    CASE 
        WHEN price_currency='USD' THEN price_amount * 3.9024 
        WHEN price_currency='EUR' THEN price_amount * 4.413 
        WHEN price_currency='ILS' THEN price_amount * 1 
        ELSE price_amount * 1 
    END AS final_price 
FROM items 
WHERE final_price BETWEEN 1 AND 100

but I get:

ERROR: column "final_price" does not exist
LINE 1: …ce_amount * 1 END AS final_price FROM items WHERE final_pric…

It is getting selected using alias, so why is it trying to find it in the table?

Best Answer

The WHERE clause is evaluated before aliases in the SELECT clause. WHERE is used to locate rows from the base table which are the input to all expressions in the SELECT. Only after locating the rows your CASE can be evaluated with real values and the final_price alias is assigned its value.

There are multiple ways to solve that:

  • repeat the same expression in the WHERE clause - that way it will be used to locate the rows
  • wrap your query as a subquery in another query and move the WHERE to the outer query - as you select from a derived table, the alias becomes a "real" column that way
  • (this would work in MySQL, but not in PostgreSQL) use the HAVING clause instead of WHERE - HAVING (in MySQL) works after the SELECT is computed to do a "second wave" of filtering. In standard SQL (and in Postgres), SELECT is logically processed after HAVING, so this won't work.
  • create a generated/virtual column from your CASE expression, that way it becomes a part of your base table and you might even be able to index it

Warning: your query will need to do fulltable/index scan unless you can add some "simpler" WHERE conditions or be able to use the indexed virtual column.

Related Question