Skip site navigation (1) Skip section navigation (2)

Re: using calculated column in where-clause

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Patrick Scharrenberg" <pittipatti(at)web(dot)de>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: using calculated column in where-clause
Date: 2008-06-17 21:15:13
Message-ID: dcc563d10806171415p6c909d71n935e5d00cbeb389b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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.

In response to

pgsql-sql by date

Next:From: Patrick ScharrenbergDate: 2008-06-18 05:31:32
Subject: Re: using calculated column in where-clause
Previous:From: Andreas KretschmerDate: 2008-06-17 21:10:41
Subject: Re: using calculated column in where-clause

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group