Re: BUG #5356: citext not acting like case insensitive search

On Mar 1, 2010, at 9:15 AM, Michael Gould wrote:

>
> The following bug has been logged online:
>
> Bug reference: 5356
> Logged by: Michael Gould
> Email address: mgould(at)intermodalsoftwaresolutions(dot)net
> PostgreSQL version: 8.4.2
> Operating system: Windows 2008 R2 Server, Windows 7 x64
> Description: citext not acting like case insensitive search
> Details:
>
> When we run the following query, we return no results.
>
> Select citystateinfoid from iss.citystateinfo where cityname =
> 'JACKSONVILLE' and statecode = 'FL';
>
> However this does work
>
> Select citystateinfoid from iss.citystateinfo where cityname =
> 'Jacksonville' and statecode = 'FL'
>
> The second query is how the data is actually stored. I've already forwarded
> our ddl to david wheeler.

Michael managed to fix this issue by moving citext to the "public" schema. The question is, why would citext operators work in the public schema but not when they're in some other schema? Is `=` resolving to `TEXT = TEXT` if the "iss" schema isn't in the search path?

Michael, does this work?

Select citystateinfoid from iss.citystateinfo where cityname iss.=
'JACKSONVILLE' and statecode iss.= 'FL';

Best,

David