Using an Alias Column 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

Reference alias in WHERE clause

This is not possible as in sql, the order of execution is first, the where clause and then the select. At the time where clause is getting executed, it does not know what you have defined as an alias and so you will get that error.

You need to rewrite your query like this..

SELECT
    SUBSTRING(pk, 6, 2)::INT AS _year
FROM
    listing
WHERE
SUBSTRING(pk, 6, 2)::INT > 90

How to use the alias of my calculated parameter (in SELECT clause) in WHERE clause with PostgreSQL to prevent repetition?

You can't reuse a select alias in the where clause. You need to repeat the expression, or use a subquery or cte.

For what it's worth, you can use between to have the expression just once instead of twice in the where clause:

SELECT date_part('year', CURRENT_DATE) - f.birth_year AS age
FROM public.foo f
WHERE date_part('year', CURRENT_DATE) - f.birth_year BETWEEN 20 AND 30

As for the subquery solution, that would be:

SELECT *
FROM (
    SELECT date_part('year', CURRENT_DATE) - f.birth_year AS age
    FROM public.foo
) f
WHERE age BETWEEN 20 AND 30

Why do I get an error querying from column alias?

In postgres document:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

That's according to the SQL standard and may not be very intuitive. The (historic) reason behind this is the sequence of events in a SELECT query. WHERE and HAVING are resolved before column aliases are considered, while GROUP BY and ORDER BY happen later, after column aliases have been applied.
Also note that conflicts between input and output names are resolved differently in ORDER BY and GROUP BY - another historic oddity (with a reason behind it, but potentially confusing nonetheless).

You can use one of the below manners:

1. Use full both column name

SELECT first_name || ' ' || last_name AS fullname
FROM actor
WHERE first_name || ' ' || last_name BETWEEN :conditio1 AND :conditio2

2. Use CTE

WITH data s (
  SELECT first_name || ' ' || last_name AS fullname
  FROM actor
)
SELECT *
FROM data
WHERE fullname BETWEEN :conditio1 AND :conditio2

3. Use subquery

SELECT *
FROM (
  SELECT first_name || ' ' || last_name AS fullname
  FROM actor
) tmp
WHERE tmp.fullname BETWEEN :conditio1 AND :conditio2

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