Using an Alias column in the where clause in Postgresql

Question

I have a query like this:

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_state = 'NEW') 

Which gives the following error:

PGError: ERROR:  column "lead_state" does not exist
LINE 1: ...s.id AND lead_informations.mechanic_id = 3 WHERE (lead_state...

In MySql this is valid, but apparently not in Postgresql. From what I can gather, the reason is that the SELECT part of the query is evaluated later than the WHERE part. Is there a common workaround for this problem?

Answer 1

In MySql this is valid, but apparently not in Postgresql. From what I can gather, the reason is that the SELECT part of the query is evaluated later than the WHERE part. Is there a common workaround for this problem?

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

Answer 2

I believe the common solution is to use an inner SELECT for the calculation (or CASE statement in this case) so that the result of the inner SELECT is available to the entire outer query by the time the execution gets to that query. Otherwise, the WHERE clause is evaluated first and knows nothing about the SELECT clause.

Answer 3

You can just repeat the CASE statement in the WHERE 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 
    CASE WHEN lead_informations.state IS NOT NULL 
    THEN lead_informations.state ELSE 'NEW' END = 'NEW'