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

Question

In the query below, the parameter date_part('year', CURRENT_DATE) - f.birth_year is repeated three times. How can I replace it by its alias age ?

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

That's not the right way to get age, at least in the United States. — shawnt00, Jun 19 '20 at 16:19

score 1 · Accepted Answer · answered Jun 19 '20 at 15:02

1

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

answered Jun 19 '20 at 15:02

GMB

216,147
25
84
135

does `BETWEEN x AND y` include `x` and `y` ? – DevonDahon Jun 19 '20 at 15:04
@DevonDahon: yes, it is inclusive on both ends. – GMB Jun 19 '20 at 15:04

score 0 · Answer 2 · answered Jun 19 '20 at 16:18

0

A CTE lets you name expressions so you can avoid repeating them. By the way, that's not the correct calculation for age:

with data as (
    SELECT date_part('year', CURRENT_DATE) - birth_year AS age
    FROM public.foo
)
select * from data
WHERE age >= 20 AND <= 30;

answered Jun 19 '20 at 16:18

shawnt00

16,443
3
17
22

Check out the [age](https://www.postgresql.org/docs/current/functions-datetime.html) function. – Belayer Jun 20 '20 at 00:29
If you haven't had your birthday yet that year it's off by one. – shawnt00 Jun 20 '20 at 03:14

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

2 Answers2