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

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 (view raw or flat)
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

pgsql-novice by date

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

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