Column does not exist in psql

Question

I am trying to filter out days with more than 1% error rates for a website. I already have a table that shows the individual days with their respective error rates, but when I try to include a "where" or "having" clause to filter out the days with a ratio below .01, the query stops working and it says my column does not exist, even though I declared it a few characters before. This is the code:

select date(time) as day, 
    (trunc(cast(count(*) filter (where status similar to '%404%') as decimal) / count(*), 5)) as col1 
    from log
    where col1 > 0.01 
    group by day 
    order by col1 desc;

This is the error i get

ERROR:  column "col1" does not exist
LINE 4: where col1 > 0.01

Thanks!!

I have already tried changing the `where` to a `having` and placing it after the group by but I get the same error @GordonLinoff — Diego Villafuerte, Oct 26 '18 at 18:45

Gordon Linoff · Answer 1 · 2018-10-26T19:00:44.980

col1 is the result of an aggregation. Postgres allows aliases for columns in the group by, but not having. So move the condition to a having clause:

select date(time) as day, 
      (trunc(cast(count(*) filter (where status similar to '%404%') as decimal) / count(*), 5)) as col1 
from log
group by day 
having (trunc(cast(count(*) filter (where status similar to '%404%') as decimal) / count(*), 5)) > 0.01 
order by col1 desc;

Although filter is really fancy, I think this version of the logic is simpler:

      trunc(cast(avg( (status similar to '%404%')::decimal), 5) as col1

It is also easier to fit into the having clause.

I tried with having, but get the same result. (Col1 does not exist) — Diego Villafuerte, Oct 26 '18 at 18:40

Aaron Dietz · Answer 2 · 2018-10-26T19:03:43.727

The issue is that you can't reference a column alias col1 in a WHERE clause, unless you layer the query.

Repeated condition option:

select date(time) as day, 
       (trunc(cast(count(*) filter (where status similar to '%404%') as decimal) / count(*), 5)) as col1 
from log
group by day 
having (trunc(cast(count(*) filter (where status similar to '%404%') as decimal) / count(*), 5)) > 0.01
order by col1 desc;

Derived table option:

select day, col1
from (select date(time) as day, 
             (trunc(cast(count(*) filter (where status similar to '%404%') as decimal) / count(*), 5)) as col1 
      from log
      group by day) as derivedTable
where col1 > 0.01

Column does not exist in psql

2 Answers2