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?

Gordon Linoff · Accepted Answer · 2020-03-02 02:48:54Z

3

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;

answered Mar 2, 2020 at 2:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mandroid Over a year ago

Does it mean we can't pass aliases to functions()?

Gordon Linoff Over a year ago

@Mandroid . . . No, it means that you cannot re-use an alias in the same select (or where) that defines it. You could use a CTE or subquery as well.

Collectives™ on Stack Overflow

Reusing alias for an expression in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related