This project is archived and is in readonly mode.
Add support for PostgreSQL citext column type
I've just migrated from MySQL to PostgreSQL and I was really surprised to find that text columns are actually case sensitive. The workarounds were ugly and awkward to do in Rails, I think, but then I found the new citext column type. It's available as an add-on for 8.3 and it's included as an add-on for 8.4.
I needed to get this going quickly so I added support for a new citext column type which, as far as Rails is concerned, behaves just like a regular text column. It occurred to me afterwards that maybe I could have implemented it as a :case_sensitive option on the regular text column type to make switching between databases easier. MySQL doesn't have a converse case sensitive column type (right?) so the option would be ineffective there but I still think this would be the better option. I'm short on time at the moment though and this is better than nothing for people that need it.
There are no tests because they would require the extension to be installed, not just on the filesystem, but via SQL as well. It works with my app against 2-3-stable though. I haven't tried it against master but the other tests are still passing in both cases.
-
postgresql-citext.patch
5.67 KB
-
James Le CuirotThe above patch was against master. This is against 2-3-stable.
-
postgresql-citext-2-3.patch
5.67 KB
-
-
CancelProfileIsBroken- Tag changed from 2-3-stable, activerecord, citext, master, patch, postgres, postgresql to 2-3-stable, activerecord, bugmash, citext, master, patch, postgres, postgresql
-
Rizwan Reza- State changed from new to incomplete
- Tag changed from 2-3-stable, activerecord, bugmash, citext, master, patch, postgres, postgresql to 2-3-stable, activerecord, citext, master, patch, postgres, postgresql
Tests missing.
-
James Le Cuirot
I've explained why the tests are missing. How would you approach the problem?
-
Rizwan Reza
- Assigned user set to Pratik
- Milestone cleared.
I'm assigning this ticket to Pratik, who's working on ActiveRecord these days. He'll be able to help you in this regard.
-
spovich- Importance changed from to Low
Rather than trying to redefine the column types, why not just use the postgres ILIKE command to give case-insensitive compares? For rails 3, I currently have a very small monkey patch on Arel that changes generated LIKEs to ILIKEs. Just put this in config/initializers.
require 'arel' module Arel module Predicates class Match < Binary def predicate_sql 'ILIKE' end end class NotMatch < Binary def predicate_sql 'NOT ILIKE' end end end end -
James Le Cuirot
I'm not familiar with Rails 3 yet but it was a tad annoying to have User.find_by_name not work in a case-insensitive fashion. I'm still using this patch on a recent 2-3-stable without issue.
-
-
-
Ernie MillerARel 2 will fix this come Rails 3.0.2, just FYI: https://github.com/rails/arel/commit/5ca0c9a45788a14b9f454c93cd24fb...
-
James Le Cuirot
That's fantastic. I'm slowly but surely moving over to Rails 3. ;)
-
Santiago Pastorino
James I guess this could be closed now, right?
-
James Le Cuirot
Yeah, this result is good enough for me.
-
Santiago Pastorino
- State changed from incomplete to resolved
-
Sean Kirby
I would not consider the proposed solution of using an ILIKE query to do a case insensitive match acceptable. The main reason is that Postgres will not use indexes for ILIKE queries. You can construct an index for doing case-insensitive searches (http://archives.postgresql.org/pgsql-novice/2006-07/msg00112.php) but this requires modifying all the SQL in order to make sure that the index is used.