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

I struggled on the same issue and "mysql syntax is non-standard" is not a valid argument in my opinion. PostgreSQL adds handy non-standard extensions as well, for example "INSERT ... RETURNING ..." to get auto ids after inserts. Also, repeating large queries is not an elegant solution.

However, I found the WITH statement very helpful (CTE's). It sort of creates a temporary view within the query which you can use like a usual table then. I'm not sure if I have rewritten your JOIN correctly, but in general it should work like this:

WITH jobs_refined AS (     SELECT         jobs.*,         (SELECT 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 ) SELECT * FROM jobs_refined WHERE lead_state = 'NEW' 

Answer 2

You would need to either duplicate the case statement in the where clause, or my preference is to do something like the following:

SELECT * FROM (   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 ) q1 WHERE (lead_state = 'NEW')