Usando uma coluna Alias ​​na cláusula where no Postgresql

Eu tenho uma consulta como esta:

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' 

Que dá o seguinte erro:

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

No MySql isso é válido, mas aparentemente não no Postgresql. Pelo que posso reunir, a razão é que a parte SELECT da consulta é avaliada posteriormente à parte WHERE . Existe uma solução comum para esse problema?

O suporte do MySQL é, como você experimentou, fora do padrão. A maneira correta é reimprimir a mesma expressão usada na cláusula SELECT:

 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 

Eu lutei sobre o mesmo problema e “a syntax do mysql é não-padrão” não é um argumento válido na minha opinião. O PostgreSQL também adiciona extensões não padrão, por exemplo, “INSERT … RETURNING …” para obter ids automáticos após inserções. Além disso, repetir grandes consultas não é uma solução elegante.

No entanto, achei a declaração WITH muito útil. Isso cria uma visão temporária dentro da consulta que você pode usar como uma tabela usual. Não tenho certeza se reescrevi seu JOIN corretamente, mas em geral ele deve funcionar assim:

 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' 

Você precisaria duplicar a instrução case na cláusula where ou minha preferência é fazer algo como o seguinte:

 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') 

Acredito que a solução comum é usar um SELECT interno para o cálculo (ou a instrução CASE nesse caso) para que o resultado do SELECT interno esteja disponível para toda a consulta externa no momento em que a execução chegar a essa consulta. Caso contrário, a cláusula WHERE é avaliada primeiro e não sabe nada sobre a cláusula SELECT.

Eu usei alias em onde assim. (Consulta interna).

 Select "Vendors"."VendorId", "Vendors"."Name","Result"."Total" From (Select "Trans"."VendorId", ("Trans"."A"+"Trans"."B"+"Trans"."C") AS "Total" FROM "Trans" WHERE "Trans"."Year"=2014 ) As "Result" JOIN "Vendors" ON "Result"."VendorId"="Vendors"."VendorId" WHERE "Vendors"."Class"='I' AND "Result"."Total" > 200