0

I have this query, which works:

SELECT *, ("last done" + "auto time span"::interval) AS "new last done" 
FROM personal."time tabs" 
WHERE "reset automatically" = true 
 AND ("last done" + "auto time span"::interval) < now();

However, I notice the duplicate code: ("last done" + "auto time span"::interval). Is it possible to make it only mentioned once?

These did sadly NOT work:

SELECT *, ("last done" + "auto time span"::interval) AS "new last done" 
FROM personal."time tabs" 
WHERE "reset automatically" = true 
AND "new last done" < now();

Or:

SELECT * 
FROM personal."time tabs" 
HAVING ("last done" + "auto time span"::interval) AS "new last done" 
WHERE "reset automatically" = true 
 AND "new last done" < now();

What is the right way?

1
  • The right way for many would be to leave your query as is.
    – mustaccio
    Commented Aug 12, 2020 at 19:00

1 Answer 1

1

I would just duplicate the expression. It is possible to structure your query so that you only define the expression once, but it will end up being more verbose, as it requires that you define the field you're WHEREing as a column in a subquery and then do the WHERE on the outer field.

SELECT * FROM (
  SELECT *, ("last done" + "auto time span"::interval) AS "new last done"
  FROM personal."time tabs"
  WHERE "reset automatically" = true
) AS inner_query
WHERE "new last done" < now();

As long as you don't have ORDER BY or LIMIT on the inner query, the query planner should be able to tell that it's logically equivalent to having that WHERE in the inner query, so actual query execution shouldn't be affected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.