Re: using calculated column in where-clause

On Tue, Jun 17, 2008 at 2:46 PM, Patrick Scharrenberg <pittipatti(at)web(dot)de> wrote:
> Hi!
>
> I'd like to do some calculation with values from the table, show them a
> new column and use the values in a where-clause.
>
> Something like this
> select a, b , a*b as c from ta where c=2;
>
> But postgresql complains, that column "c" does not exist.
>
> Do I have to repeat the calculation (which might be even more complex
> :-) ) in the "where"-clause, or is there a better way?

Pretty much yes. Trying to do tricks using subselects may result in
substandard performing query plans. You can always do something like:

select * from (select a,b,a*b as c) as z where z.c = 2 but if it's
slower don't blame me.

The nice thing here is that you can index on that function, which is
the real issue with performance, since otherwise you'll likely see a
sequential scan every time.

create index ta_atimesb on ta ((a*b));

and from then on the query should run pretty fast. That's really more
important than if you have to put it twice on the same query line.