On 15 June 2010 19:41, Chris Campbell
<ccampbell@cascadeds.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