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?
-
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– Federico Cristina2012-06-27 01:46:42 +00:00Commented Jun 27, 2012 at 1:46
-
1a+b is just example, in my case it is distance calculation using postgisYuri Barbashov– Yuri Barbashov2012-06-27 02:00:38 +00:00Commented Jun 27, 2012 at 2:00
Add a comment
|
2 Answers
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 BYandGROUP BYclauses, but not in theWHEREorHAVINGclauses; 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.
answered Jun 27, 2012 at 2:07
Erwin Brandstetter
668k159 gold badges1.2k silver badges1.3k bronze badges
1 Comment
Erwin Brandstetter
@YuriBarbashov: There are quite a few things that work "nicely" in MySQL - until they don't. :)
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.
6 Comments
Yuri Barbashov
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)
araqnid
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
@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.
Federico Cristina
Also, try the "Explain Query" function in pgAdmin to confirm @araqnid comment
Yuri Barbashov
i simply tested it on 100000 records db, query time is proportional to number of function call
|