Column alias in where clause?

From: Jeff Ross <jross(at)wykids(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Column alias in where clause?
Date: 2008-08-13 16:47:17
Message-ID: 48A3102C.8090403@wykids.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm a little confused about how to use a column alias in the where
clause of a query. I'm sure the answer is something simple, but I
haven't found anything searching through Google or from reading the docs.

I inherited a table that used to store the name of a facility director
as the actual name. I'm transitioning to using a pointer to a people
table, but until the transition is complete, I want to be able to show
the old information if the id is null, or the new information if the id
is not null.

I wrote this query to search the facilities by the director's name:

SELECT fc_facility_id, fc_name,
CASE
WHEN fc_director_id is null
THEN fc_director_last_name || ', ' || fc_director_first_name
WHEN fc_director_id is not null
THEN pp_last_name || ', ' || pp_first_name
END as "fc_director_name",
CASE
WHEN fc_director_id is null
THEN fc_director_last_name
WHEN fc_director_id is not null
THEN pp_last_name
END as "fc_director_last_name",
CASE
WHEN fc_director_id is null
THEN fc_director_first_name
WHEN fc_director_id is not null
THEN pp_first_name
END as "fc_director_first_name",
fc_mailing_city, fc_type, fc_license_end_date, fc_license_status
FROM facilities
LEFT JOIN people ON fc_director_id = pp_id
WHERE fc_director_name ilike ('%Cobb%');

but I get this error

ERROR: column "fc_director_name" does not exist
LINE 23: WHERE fc_director_name ilike ('%Cobb%');

I've also written the where clause using double quotes around the column
name but I get the same error.

The documentation for SELECT says that "When an alias is provided, it
completely hides the actual name of the table or function; for example
given FROM foo AS f, the remainder of the SELECT must refer to this FROM
item as f not foo.".

Does this apply in the WHERE clause as well? If it doesn't how can I
refer to the results of the case statements later in the where clause?

Thanks,

Jeff Ross

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2008-08-13 16:53:37 Re: automatic REINDEX-ing
Previous Message Kevin Hunter 2008-08-13 16:41:41 Re: automatic REINDEX-ing