#3174 Add support for PostgreSQL citext column type - Ruby on Rails

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

This project is archived and is in readonly mode.

#3174 ✓resolved

Add support for PostgreSQL citext column type

Reported by James Le Cuirot | September 9th, 2009 @ 04:09 PM | in 3.0.2

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.7 KB

Comments and changes to this ticket

You flagged this item as spam.
James Le Cuirot September 9th, 2009 @ 04:09 PM
The above patch was against master. This is against 2-3-stable.
- postgresql-citext-2-3.patch 5.7 KB
CancelProfileIsBroken September 25th, 2009 @ 12:28 PM
- 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 January 21st, 2010 @ 06:42 AM
- 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.
You flagged this item as spam.
James Le Cuirot January 21st, 2010 @ 10:13 AM
I've explained why the tests are missing. How would you approach the problem?
Rizwan Reza January 21st, 2010 @ 10:17 AM
- 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.
You flagged this item as spam.
spovich August 6th, 2010 @ 03:55 AM
- 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
```
You flagged this item as spam.
James Le Cuirot August 6th, 2010 @ 09:12 AM
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.
Jeremy Kemper August 30th, 2010 @ 04:10 AM
- Milestone cleared.
[bulk edit]
Jeremy Kemper October 15th, 2010 @ 11:01 PM
- Milestone set to “3.0.2”
[bulk edit]
You flagged this item as spam.
Ernie Miller November 4th, 2010 @ 11:55 PM
ARel 2 will fix this come Rails 3.0.2, just FYI: https://github.com/rails/arel/commit/5ca0c9a45788a14b9f454c93cd24fb...
You flagged this item as spam.
James Le Cuirot November 5th, 2010 @ 12:05 AM
That's fantastic. I'm slowly but surely moving over to Rails 3. ;)
Santiago Pastorino November 5th, 2010 @ 02:18 AM
James I guess this could be closed now, right?
You flagged this item as spam.
James Le Cuirot November 5th, 2010 @ 11:03 AM
Yeah, this result is good enough for me.
Santiago Pastorino November 5th, 2010 @ 02:50 PM
- State changed from “incomplete” to “resolved”
You flagged this item as spam.
Sean Kirby March 21st, 2011 @ 07:52 PM
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.