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

you can't filter by alias in postgre, see here: ```https://dba.stackexchange.com/questions/225874/using-column-alias-in-a-where-clause-doesnt-work``` — Kristian, Sep 17 '20 at 09:24
Does this answer your question? [Using an Alias column in the where clause in Postgresql](https://stackoverflow.com/questions/3241352/using-an-alias-column-in-the-where-clause-in-postgresql) — , Sep 17 '20 at 09:59

JGW · Accepted Answer · 2020-09-17T11:02:57.953

2

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

edited Sep 17 '20 at 11:02

answered Sep 17 '20 at 10:52

JGW

314
4
18

score 1 · Answer 2 · answered Sep 17 '20 at 10:23

1

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.

answered Sep 17 '20 at 10:23

Jim Jones

18,404
3
35
44

Note that the above solution is much more efficient than mine below, however, if you specifically want to filter on an alias, you need to store that data in some sort of table first. – JGW Sep 17 '20 at 10:59

Can't filter by alias

2 Answers2