Re: Mixed case text searches

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