Re: Mixed case text searches

From: Chris Campbell <ccampbell(at)cascadeds(dot)com>
To: Thom Brown <thombrown(at)gmail(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Mixed case text searches
Date: 2010-06-16 15:58:54
Message-ID: 453A24085F801842AEA8D0B6B269065D2F8BA64D32@HDMC.cds.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On 15 June 2010 19:41, Chris Campbell <ccampbell(at)cascadeds(dot)com<mailto: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<http://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

--
Okay, apparently it makes a difference which schema you import the functions into. I did not originally import them into the public schema of my database. In order to use the citext type I had to prefix it with my schema name, which apparently effected its behavior. Once I removed those functions from my working schema and added them to the public schema it started working for me.

Thanks for your assistance!

"Are you sure you're referring to the correct field in your where clause?"
Hehe, yeah pretty sure.

- CBC

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message John Gage 2010-06-16 19:02:44 DISTINCT not working...the way I want it to
Previous Message Tom Wilcox 2010-06-16 14:02:43 Re: libpython - cannot open shared object file