Using an Alias column in the where clause in Postgresql
Asked Answered
A

6

107

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?

Absorptance answered 13/7, 2010 at 20:41 Comment(2)
It's a good question, but a bizarre example query. You never want to select a non-NULL value for that column, so the whole CASE statement is entirely unnecessary.Lalapalooza
@Lalapalooza You're right. The query is dynamically generated, so the where clause might contain something else, but the select part would remain the same.Absorptance
H
17

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
Hyaena answered 13/7, 2010 at 20:46 Comment(2)
Additionally, you could replace the CASE statement with COALESCE: COALESCE(lead_informations.state, 'NEW') AS lead_state.Hyaena
Seems a bit awkward to have to duplicate the logic like that, but I guess I'll just do that then. Didn't know about COALESCE - Thanks for that tip.Absorptance
C
106

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'
Caffeine answered 11/11, 2011 at 14:32 Comment(4)
saying something is "non-standard" is completely valid when you're looking to do something across two different products. they both implement parts of the SQL standard(s) and both have non-standard extensions. don't expect the non-standard extensions to translate across. DO expect the SQL standard portions to translate. that being said - thanks for the WITH example.Wormwood
There is also a difference when the standard says one thing, and you do something else vs. extensions to the product where the standard is silent.Rauscher
This is the correct answer to the question. The only reason the accepted answer works is because the column the WHERE clause is on actually exists on the joined table which does not address an alias of a query created column such as an alias of a sub-query.Thad
Be aware that using a CTE like this, vs a sub-query can cause a slightly different row-ordering (but same overall effect), unless you have an order by clause :)Looming
C
30

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')
Cervix answered 12/10, 2012 at 19:50 Comment(0)
H
17

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
Hyaena answered 13/7, 2010 at 20:46 Comment(2)
Additionally, you could replace the CASE statement with COALESCE: COALESCE(lead_informations.state, 'NEW') AS lead_state.Hyaena
Seems a bit awkward to have to duplicate the logic like that, but I guess I'll just do that then. Didn't know about COALESCE - Thanks for that tip.Absorptance
H
3

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.

Heptad answered 13/7, 2010 at 20:46 Comment(0)
N
0

I used alias in where like this. (Sub Query).

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
Nonobjective answered 8/10, 2015 at 10:27 Comment(1)
You can just say Total > 200, but specifying the subquery name could be a nice disambiguator :)Looming
O
0

Subquery:

SELECT "tab_1"."BirthDate", "tab_1"."col_1" FROM (
   SELECT BirthDate, DATEADD(year, 18, BirthDate) AS "col_1" FROM Employees
) AS "tab_1"
WHERE "tab_1"."col_1" >= '2000-12-31';
Odyssey answered 10/4, 2019 at 20:46 Comment(3)
Answers with code are usually best accompanied by an explanation of why what you posted works and how it addresses the question specificallyUnmoor
@Unmoor The comment is self-explanatoryCerebrospinal
@GianGomen maybe not to new users, especially if the guide on how to answer was missed. It really helps other people that might come across this answer later. It's like how you might argue that the guidelines on how to ask a question are self-explanatory, but bad questions are asked a lot, so giving a bit of helpful advice can nudge people in the right directionUnmoor

© 2022 - 2025 — McMap. All rights reserved.