SELECT AS column does not exist in WHERE clause

Question

I am using Postgres to select values from a relation in my database. I would like to have additional columns that do not exist in the original relation, so I achieve this using a query similar to the one below:

SELECT table1.*, table1.title || table1.first_name || table1.last_name AS full_name, table2.email AS user_email FROM (table1 JOIN table2 ON table1.id = table2.id) WHERE table1.type = 'Open' AND user_email ILIKE '%test%';

When I try to do this, I get the following error:

error: column "user_email" does not exist

Does anyone know a way around this?

Answer 1

You cannot use the specified alias directly in the WHERE clause.

You can use the original column name instead:

WHERE table1.type = 'Open' AND table2.emailILIKE '%test%';