Reusing alias for an expression in postgresql

Question

I am trying to do this:

select first_name || ' ' || last_name as full_name, Length(full_name) as len from customer order by len

It is not possible;

column "full_name" does not exist

So, I have to do this:

select first_name || ' ' || last_name as full_name, Length(first_name || ' ' || last_name) as len from customer order by len

Does it mean sql engine has to compute expression 'first_name || ' ' || last_name' two times?

Answer 1

As you observe, what you want to do is not possible. Instead, you can use a lateral join to calculate values in the FROM clause:

select v.full_name, Length(v.full_name) as len
from customer c cross join lateral
     (values (first_name || ' ' || last_name)
     ) v(full_name)
order by len;