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?

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. — phils, Sep 04 '12 at 05:07
@phils You're right. The query is dynamically generated, so the `where` clause might contain something else, but the `select` part would remain the same. — troelskn, Sep 04 '12 at 08:06

score 104 · Answer 1 · edited Oct 22 '21 at 18:21

104

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'

edited Oct 22 '21 at 18:21

rogerdpack

62,887
36
269
388

answered Nov 11 '11 at 14:32

Marten Lehmann

1,041
2
7
2

17

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. – Messy Dec 29 '11 at 14:26
1

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. – Kirk Roybal Oct 27 '14 at 23:04
3

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. – Scap Jul 02 '20 at 12:51
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 :) – rogerdpack Oct 25 '21 at 16:04

score 29 · Answer 2 · edited Oct 22 '21 at 18:16

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

score 17 · Accepted Answer · edited Jan 26 '18 at 01:30

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

edited Jan 26 '18 at 01:30

Dai

141,631
28
261
374

answered Jul 13 '10 at 20:46

OMG Ponies

325,700
82
523
502

6

Additionally, you could replace the CASE statement with COALESCE: `COALESCE(lead_informations.state, 'NEW') AS lead_state`. – OMG Ponies Jul 13 '10 at 20:54
4

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. – troelskn Jul 14 '10 at 10:10

score 3 · Answer 4 · answered Jul 13 '10 at 20:46

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.

score 0 · Answer 5 · edited Oct 22 '21 at 18:18

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

edited Oct 22 '21 at 18:18

rogerdpack

62,887
36
269
388

answered Oct 08 '15 at 10:27

M Sohail Maroof

23
4

You can just say `Total > 200`, but specifying the subquery name could be a nice disambiguator :) – rogerdpack Oct 22 '21 at 18:19

score 0 · Answer 6 · edited Oct 22 '21 at 18:18

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

edited Oct 22 '21 at 18:18

rogerdpack

62,887
36
269
388

answered Apr 10 '19 at 20:46

Douglas Rosa

71
3

10

Answers with code are usually best accompanied by an explanation of why what you posted works and how it addresses the question specifically – C.Nivs Apr 10 '19 at 21:10
@C.Nivs The comment is self-explanatory – Hanako Jun 07 '19 at 15:18
@GianGomen maybe not to new users, especially if the guide on [how to answer](https://stackoverflow.com/help/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 direction – C.Nivs Jun 07 '19 at 15:47

Using an Alias column in the where clause in Postgresql

6 Answers6

Linked

Related