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: "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar>
Cc: "Patrick Scharrenberg" <pittipatti(at)web(dot)de>, pgsql-sql(at)postgresql(dot)org
Subject: Re: using calculated column in where-clause
Date: 2008-06-18 20:47:08
Message-ID: dcc563d10806181347q1a72c861i6a2d99f38853fee8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Wed, Jun 18, 2008 at 1:35 PM, Fernando Hevia <fhevia(at)ip-tel(dot)com(dot)ar> wrote:
>
>> -----Mensaje original-----
>> De: pgsql-sql-owner(at)postgresql(dot)org
>> [mailto:pgsql-sql-owner(at)postgresql(dot)org] En nombre de Patrick
>> Scharrenberg
>> Enviado el: Martes, 17 de Junio de 2008 17:46
>> Para: pgsql-sql(at)postgresql(dot)org
>> Asunto: [SQL] using calculated column in where-clause
>>
>> 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?
>>
>
> For complex calculations I have obtained better performance using nested
> queries. For example:
>
> select a, b, c select
>   ( select a, b, a*b as c from ta) subquery1
> where c = 2;
>
> This nesting is probably overhead in such a simple case as this, but in more
> complex ones and specially with volatile functions it will provide an
> improvement.

I was under the impresion from previous discussions that the query
planner flattened these out to be the same query.  Do you get
different query plans when you re-arrange this way?

In response to

Responses

pgsql-sql by date

Next:From: Fernando HeviaDate: 2008-06-19 18:23:48
Subject: Re: using calculated column in where-clause
Previous:From: Fernando HeviaDate: 2008-06-18 19:35:48
Subject: Re: using calculated column in where-clause

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