A Helpful Suggestion For Postgres And Cgridview

Hi all,

I just spent about 30 minutes trying to figure out a way around tons of coding work to either ILIKE everything or to LOWER() everything to be able to do case insensitive comparisons in Postgres (mainly so my CGridView filters would work out of the box). I also saw some people posting in the forum, but I did not see this solution posted. I thought I would add it in case it helps someone.

I figured out that citext comes prepackaged with most postgres installs, but not enabled. You can simply do the following to make this happen:

  • Open pgadmin

  • Connect to the server and database you are using

  • Open an sql window

  • Run the command (without quotes): "CREATE EXTENSION citext;"

  • Change any column types in your tables from character varying or text to citext

Now all of your datagrid filter stuff will work! The only downside is that you can never do case sensitive comparisons on these columns that you changed to citext, but I never need to do that anyway.

Hope this helps someone with CGridView Filter case insensitivity problems while using Postgres!

you may also override CDbCriteria and overload addSearchCondition and compare functions changing LIKE to ILIKE and NOT LIKE to NOT ILIKE. then use this new criteria class in your search() function in model classes.

I think it is better solution because you can still search with case sensitive operators.