Creating a column in SELECT and then filtering it with WHERE (PostgreSQL)

Question

I'm trying to compute the difference between two columns, and then filter the results within a WHERE command.

Like this:

SELECT abs("c1" - "c2") as diff
    FROM table1
    WHERE diff < 3

I get a "ERROR: column "diff" does not exist".

How should I set it up so I can reuse the newly created column "diff" to filter the results?

Answer 1

Alias can not be used in WHERE clause. It is used in GROUP BY and ORDER BY clause.

Use alias column and subquery

SELECT t.*
FROM (SELECT abs("c1" - "c2") as diff
      FROM table1) t
WHERE t.diff < 3

Use abs() in where clause

SELECT ABS(c1 - c2) diff
FROM table1
WHERE ABS(c1 - c2) < 3

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=f16cace0c8c733a4b0c7627fcfd5b7c3

Answer 2

This is standard SQL behavior. You cannot reference an alias in the where clause. The traditional solutions are subqueries, CTEs, or repeat the expression. In Postgres, you can also use a lateral join:

SELECT v.diff
FROM table1 t1 CROSS JOIN LATERAL
     (VALUES (t1."c1" - t2."c2")) v(diff)
WHERE v.diff < 3;