Can't filter by alias

Question

created_at - type timestamp with timzone

Query:

 select id, 
           created_at,
           extract(epoch from CURRENT_TIMESTAMP - created_at) as delta_sec
    from shop_order

Success work. Nice.

Now I want to show only records where delta_sec > 10000

I try this:

select id, 
       created_at,
       extract(epoch from CURRENT_TIMESTAMP - created_at) as delta_sec
from shop_order
where delta_sec > 10000

But I get error:

ERROR:  column "delta_sec" does not exist
LINE 5: where delta_sec > 10000
              ^
SQL state: 42703
Character: 125

Answer 1

Alternatively, if you want to use the alias to filter, then put the data into a CTE first:

WITH cte_name AS (
     SELECT
      id, 
      created_at,
      extract(epoch from CURRENT_TIMESTAMP - created_at) as delta_sec
    FROM shop_order
)
SELECT *
    FROM cte_name
    WHERE delta_sec > 10000

Answer 2

Just repeat the expression instead of using its alias, e.g:

select 
  id, 
  created_at,
  extract(epoch from CURRENT_TIMESTAMP - created_at)
from shop_order
where extract(epoch from CURRENT_TIMESTAMP - created_at) > 10000

In case you're concerned that the database will run the expression twice and therefore slow down your query: put your mind at ease and let PostgreSQL take care of it.