Is it possible to use CASE with WHERE ?

Question

Trying to do this:

SELECT CASE WHEN field = true THEN one * another ELSE one END as case_field 
FROM table WHERE case_field >= 9000

and receive an error that case_field doesn't exist.

Is it possible to do this without duplicating CASE ?

Answer 1

Looks like PostgresSQL supports derived tables

SELECT * FROM 
(
SELECT CASE WHEN field = true THEN one * another ELSE one END as case_field 
FROM table 
) AS derived
WHERE case_field >= 9000

You might want to check that it doesn't have any adverse affect on the execution plan.

Answer 2

Use a temporary table:

SELECT *
FROM (
     SELECT 
        CASE WHEN field = true THEN one * another
        ELSE one
        END as case_field 
        FROM table
     ) a
 WHERE a.case_field >= 9000