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
--
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?”