Skip site navigation (1) Skip section navigation (2)

Re: Mixed case text searches

From: Thom Brown <thombrown(at)gmail(dot)com>
To: Chris Campbell <ccampbell(at)cascadeds(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Mixed case text searches
Date: 2010-06-15 18:50:04
Message-ID: AANLkTin2-rcu69lgi9qjrleCmUYAACg-SO_dPyPpbFku@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 15 June 2010 19:41, Chris Campbell <ccampbell(at)cascadeds(dot)com> wrote:

>  (Pg 8.4)
>
> Well I gave it a shot and for whatever reason it doesn’t work for me.  The
> table got created with accountname field of type citext.  If I run a general
> select query the field correctly shows that it’s of type citext.  However,
> when I add my where clause accountname like ‘a%’, it returns no records.  If
> I cap it (‘A%’) then I get the expected results.  I’m using a BTree index on
> the field.  Don’t know if that makes a difference or not.  Has anyone gotten
> this citext type to work?  Is there an undocumented trick to getting it to
> work?  Thanks
>
>
>
Please reply below rather than above messages and in plain text if possible.

I'm not sure why that's not working for you.  I just tried it on 8.4.4 and
was fine for me:

test=# create table test_table(
id serial,
stuff citext);
NOTICE:  CREATE TABLE will create implicit sequence "test_table_id_seq" for
serial column "test_table.id"
CREATE TABLE

test=# insert into test_table (stuff) values
('animal'),('Alpha'),('aquarium'),('Arctic');
INSERT 0 4

test=# select * from test_table where stuff like 'a%';
 id |  stuff
----+----------
  1 | animal
  2 | Alpha
  3 | aquarium
  4 | Arctic
(4 rows)

test=# select * from test_table where stuff like 'A%';
 id |  stuff
----+----------
  1 | animal
  2 | Alpha
  3 | aquarium
  4 | Arctic
(4 rows)

Are you sure you're referring to the correct field in your where clause?

Regards

Thom

In response to

Responses

pgsql-novice by date

Next:From: Bruce MomjianDate: 2010-06-15 19:49:28
Subject: Re: Windows environment
Previous:From: Chris CampbellDate: 2010-06-15 18:41:25
Subject: Re: Mixed case text searches

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group