Использование столбца Alias в предложении where в Postgresql

У меня есть такой запрос:

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

Что дает следующая ошибка:

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

В MySql это действительно, но, видимо, не в Postgresql. Из того, что я могу собрать, причина в том, что часть запроса SELECT оценивается позже, чем часть WHERE. Существует ли общий способ решения этой проблемы?

Ответ 1

Поддержка MySQL, как вы это делали, нестандартна. Правильный способ - перепечатать одно и то же выражение, используемое в предложении 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

Ответ 2

Я боролся с одной и той же проблемой и "синтаксис mysql нестандартен", по моему мнению, не является веским аргументом. PostgreSQL также добавляет удобные нестандартные расширения, например "INSERT... RETURNING...", чтобы получить идентификаторы автоматически после вставки. Кроме того, повторение больших запросов не является изящным решением.

Однако я нашел оператор WITH очень полезным. Это создает временное представление в запросе, которое вы можете использовать, как обычную таблицу. Я не уверен, правильно ли переписал ваш JOIN, но в целом он должен работать следующим образом:

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'

Ответ 3

Вам нужно будет либо продублировать оператор case в предложении where, либо мое предпочтение - сделать что-то вроде следующего:

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

Ответ 4

Я считаю, что общим решением является использование внутреннего SELECT для вычисления (или оператора CASE в этом случае), чтобы результат внутреннего SELECT был доступен для всего внешнего запроса к тому времени, когда выполнение попадет в этот запрос. В противном случае предложение WHERE сначала оценивается и ничего не знает о предложении SELECT.

Ответ 5

Я использовал псевдоним там, где это было. (Запрос INNER).

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