CODEWITHSUNDEEP
postgresqlamazon-web-servicesamazon-redshiftnetezzaamazon-redshift-spectrum
Sean Stott
Sean Stott

Reputation: 13

What is the PostgreSQL function called that allows you to reference aliased columns in SELECT statement?

My company is transitioning from Netezza to AWS/Redshift. When using Netezza, it is convenient to name a column within a select statement, and then be able to reference it again, e.g.:

Select Column1 + Column2 as Alias1, Alias1 + Column3 as Alias2 from X where Alias1 > 0

Netezza allows me to do this, however I cannot within Redshift. (I also can't do this in MS SQL). I wouldn't be able to refer to the column alias within the SELECT statement, nor would I be able to reference it in a WHERE clause.

What is the name of this functionality, and is there a way to implement it? Without this functionality, I'm forced to tediously write sub-queries.

Thanks

Upvotes: 1

Views: 82

Answers (2)

LauriK
LauriK

Reputation: 1929

You can't do it, but there is a very specific and logical reason for why not.

The phases involved in the logical processing of an SQL query are as follows in that specific order:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
DISTINCT clause
ORDER BY clause
LIMIT / OFFSET clause

So by the time your query gets to select, everything else is already done. The where part is way before the select part and hence cannot see the column aliases you have set.

You can read more from this link.

Upvotes: 1

AlexYes
AlexYes

Reputation: 4208

you can't do it but you can write CTEs which are similar to subqueries but arranged in logical order:

WITH
q1 as (
    select Column1 + Column2 as Alias1, Column3
    from x
)
select *, Alias1 + Column3 as Alias2
from q1
where Alias1>0

Upvotes: 0

Related Questions