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

The following bug has been logged online:

Bug reference:      5356
Logged by:          Michael Gould
Email address:      mgould@intermodalsoftwaresolutions.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.

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

>=20
> The following bug has been logged online:
>=20
> Bug reference:      5356
> Logged by:          Michael Gould
> Email address:      mgould@intermodalsoftwaresolutions.net
> PostgreSQL version: 8.4.2
> Operating system:   Windows 2008 R2 Server, Windows 7 x64
> Description:        citext not acting like case insensitive search
> Details:=20
>=20
> When we run the following query, we return no results.
>=20
> Select citystateinfoid from iss.citystateinfo where cityname =3D
> 'JACKSONVILLE' and statecode =3D 'FL';
>=20
> However this does work
>=20
> Select citystateinfoid from iss.citystateinfo where cityname =3D
> 'Jacksonville' and statecode =3D 'FL'
>=20
> The second query is how the data is actually stored.  I've already forwar=
ded
> 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 n=
ot when they're in some other schema? Is `=3D` resolving to `TEXT =3D TEXT`=
 if the "iss" schema isn't in the search path?

Michael, does this work?

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

Best,

David

"David E. Wheeler" <david@kineticode.com> writes:
> 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?

Are they in the search path?

> Michael, does this work?

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

The syntax is OPERATOR(iss.=) not just iss.= ...

            regards, tom lane

"David E. Wheeler" <david@kineticode.com> writes:
> 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?

Now that I think a bit, not having the operators in the search path
would cause exactly that.  The parser will be faced with
    citext = unknown_literal
and the only operator that it can find that is relevant at all is
text = text, which can be applied by using the implicit cast from
citext to text.  So that's what it will do.

Not sure if there is anything we can do to make this much nicer.
Removing the implicit cast seems like a cure worse than the disease,
particularly because it would result in throwing a "no such operator"
error rather than actually doing what Michael would like.

            regards, tom lane

On Mar 1, 2010, at 10:25 AM, Tom Lane wrote:

> Not sure if there is anything we can do to make this much nicer.
> Removing the implicit cast seems like a cure worse than the disease,
> particularly because it would result in throwing a "no such operator"
> error rather than actually doing what Michael would like.

Yes, I suspect that he can solve his problem by adding the iss schema to se=
arch_path.

Best,

David