Access a Column Aliases in the Where Clause in Postgresql

Using an Alias column in the where clause in Postgresql

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

access a column aliases in the where clause in postgresql

Here is how you did it - alias in the column name

select 
  -- etc etc 
  (select innerDLI.datetime_created from distribution_line_items innerDLI where innerDLI.item_number = distribution_line_items.item_number order by innerDLI.datetime_created asc limit 1) as Origination,
  -- etc etc
from distribution_stop_information
  -- etc etc

Here is how you can put it in the where, alias in the join

select 
  -- etc etc 
  Origination.datetime_created
  -- etc etc
from distribution_stop_information 
left join distribution_line_items AS Origination ON Origination.item_number = distribution_line_items.item_number
where Origination.datetime_created > to_date(?, 'YYYY-MM-DD') - interval '180 days'  
-- etc etc

There is nothing about this that is better than the solution you posted (the sql optimizer should result in the same plan) but it is "using an alias in the where clause"

Accessing column alias in postgresql

In general, you can't refer to an aggregate column's alias later in the query, and you have to repeat the aggregate

If you really want to use its name, you could wrap your query as a subquery

SELECT * 
FROM
(
    SELECT DISTINCT robber.robberid, nickname, count(accomplices.robberid)  
    AS count1 FROM robber                   
    INNER JOIN accomplices  
    ON accomplices.robberid = robber.robberid  
    GROUP BY robber.robberid, robber.nickname  
) v
ORDER BY count1 desc

Referring to a Column Alias in a WHERE Clause

SELECT
   logcount, logUserID, maxlogtm,
   DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE ( DATEDIFF(day, maxlogtm, GETDATE() > 120)

Normally you can't refer to field aliases in the WHERE clause. (Think of it as the entire SELECT including aliases, is applied after the WHERE clause.)

But, as mentioned in other answers, you can force SQL to treat SELECT to be handled before the WHERE clause. This is usually done with parenthesis to force logical order of operation or with a Common Table Expression (CTE):

Parenthesis/Subselect:

SELECT
   *
FROM
(
   SELECT
      logcount, logUserID, maxlogtm,
      DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
   FROM statslogsummary   
) as innerTable
WHERE daysdiff > 120

Or see Adam's answer for a CTE version of the same.

---

Related Topics