Postgres column does not exist but it's there with alias

Question

I have a generated Postgres query

select ..., s.name as supplier, ...
from ... supplier s ...
where ...
      AND (cast(supplier as varchar(255)) ilike '%test%')
      ...
ORDER BY supplier desc nulls last

I get this error:

org.postgresql.util.PSQLException: ERROR: column "supplier" does not exist

if I replace the "supplier" with s.name then it's ok but I can't do that. I have this issue on other columns too but there are columns which are working fine.

There are some restriction that I can't use alias the same as the table name or what can be the problem?

That is correct. A column alias defined in the `SELECT` cannot be referenced in the `WHERE`. In Postgres, it can be referenced in the `GROUP BY` and `ORDER BY` clauses (and `HAVING`) clauses. — Gordon Linoff, Jun 17 '15 at 15:54

score 20 · Answer 1 · edited Feb 21 '18 at 02:13

20

I found the explanation here: http://www.postgresql.org/message-id/18195.1038530280@sss.pgh.pa.us

I can't use the alias from the select in the WHERE clause.

edited Feb 21 '18 at 02:13

Lee Chee Kiam

11,450
10
65
87

answered Jun 17 '15 at 15:59

Zsolt Balogh

595
1
5
12

34

Is there any way around this? – Bill Brower Apr 25 '17 at 19:42
You can create a Common Table Expressions (with {table_name} as (select ...)) and then use your aliased column inside the query. – User1234 Nov 28 '21 at 07:47
Helpful link! Thank you. – Erika Oct 09 '22 at 14:33

Postgres column does not exist but it's there with alias

1 Answers1