10

For example using SQL I can do:

SELECT (a+b) as c FROM table WHERE c < 5 AND (c*c+t) > 100;

Is there any way to do that using Postgres?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Yuri Barbashov
  • 5,407
  • 1
  • 24
  • 20
  • Can't you just use: SELECT (a+b) FROM table WHERE (a+b) < 5 AND ((a+b)*(a+b)+t) > 100;?? I understand that it would be easier the way you suggest, but I just tried and it does not work. – Federico Cristina Jun 27 '12 at 01:46
  • 1
    a+b is just example, in my case it is distance calculation using postgis – Yuri Barbashov Jun 27 '12 at 02:00

2 Answers2

14

You cannot do that. Neither in PostgreSQL nor in standard SQL. The manual:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

One could debate the standard in this regard. Hugh Darwen goes to great lengths doing that in this article I was recently referred to.

You can use a subquery like Federico suggested, or a Common Table Expression (CTE) like this:

WITH cte AS (SELECT a + b AS c FROM x)
SELECT c
FROM   cte
WHERE  c < 5
AND    (c*c+t) > 100;

CTEs are especially useful for more complex operations or if you want to reuse the interim result in multiple (sub-)queries.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
3

This could be an alternative you might use:

SELECT foo.c
FROM (
    SELECT (a+b) as c FROM table
) as foo
WHERE foo.c < 5 
AND (foo.c*foo.c+t) > 100

From a performance point of view, I think it's not an optimal solution (because of the lack of WHERE clause of foo subquery, hence returning all table records). I don't know if Postgresql does some query optimization there.

Federico Cristina
  • 2,203
  • 1
  • 19
  • 37
  • The only reason i asked that because of performance reason, and i think that performing distance calculation several times in the same query is not good at all) – Yuri Barbashov Jun 27 '12 at 02:04
  • 3
    yes, postgresql will realise that `foo.c < 5` is in fact `(table.a+table.b) < 5`. if you set this up as a test and create an index on `(a+b)`, you'll see that `(a+b)<5` appears as an "Index Cond" – araqnid Jun 27 '12 at 02:05
  • 1
    @YuriBarbashov probably the best way to be sure is to wrap the calculation in a function, and have it `RAISE INFO` to see how many times it gets called. You can also experiment with setting the stable vs immutable properties that way, too. – araqnid Jun 27 '12 at 02:06
  • Also, try the "Explain Query" function in pgAdmin to confirm @araqnid comment – Federico Cristina Jun 27 '12 at 02:15
  • i simply tested it on 100000 records db, query time is proportional to number of function call – Yuri Barbashov Jun 27 '12 at 02:23
  • And i have tried your variant, but postgres reverts it back to useing several function calls)))) thats funny) – Yuri Barbashov Jun 27 '12 at 02:33