Expression alias not recognized in WHERE clause (ERROR: relation "p" does not exist)

From: Stefan Keller <sfkeller(at)gmail(dot)com>
To: pgsql-general List <pgsql-general(at)postgresql(dot)org>
Subject: Expression alias not recognized in WHERE clause (ERROR: relation "p" does not exist)
Date: 2012-08-09 11:32:43
Message-ID: CAFcOn2948+FwwpmTV73FG_58ABSeJm6FoAF5UGkgq2y8_eO_Ew@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I have two (hopefully) equivalent - and unfortunately very slow - queries which
"Select all buildings that have >1 pharmacies and >1 schools within 1000m".

In the first query there is an expression alias "b" and in the second
there are two expression aliases: "b" and "p".

Can someone tell me, why expression alias "p" is *not* recognized in
the WHERE clause - whereas alias "b" is (parantheses missing)?

And, has anyone an idea on how to reformulate this second query?

Stefan

SELECT way AS building_geometry
FROM
(SELECT osm_id, way
FROM osm_polygon
WHERE tags @> hstore('building','yes')
) AS b
WHERE
(SELECT count(*) > 1 FROM osm_poi AS p
WHERE p.tags @> hstore('amenity','pharmacy')
AND ST_DWithin(b.way,p.way,1000)
)
AND
(SELECT count(*) > 1 FROM osm_poi AS p
WHERE p.tags @> hstore('amenity','school')
AND ST_DWithin(b.way,p.way,1000)
)

SELECT b.way AS building_geometry
FROM
(SELECT way FROM osm_polygon
WHERE tags @> hstore('building','yes')
) AS b,
(SELECT way, tags->'amenity' as value FROM osm_poi
WHERE (tags ? 'amenity')
) AS p
WHERE
(SELECT count(*) > 1 FROM p
WHERE p.value = 'pharmacy'
AND ST_DWithin(b.way,p.way,1000)
)
AND
(SELECT count(*) > 1 FROM p
WHERE p.value = 'school'
AND ST_DWithin(b.way,p.way,1000)
)

ERROR: relation "p" does not exist
LINE 10: (SELECT count(*) > 1 FROM p
^

********** Error **********

ERROR: relation "p" does not exist
SQL state: 42P01
Character: 245

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2012-08-09 12:23:57 Re: Expression alias not recognized in WHERE clause (ERROR: relation "p" does not exist)
Previous Message Sergey Konoplev 2012-08-09 07:30:04 Re: Problem running "ALTER TABLE...", ALTER TABLE waiting