Rails & Postgresql case insensitive data fields

Rails Forum
,
1

I didn’t know about citext fields in Postgres. Here’s a nice solution for when you need a case insensitive unique field.

2 Likes
2

I’ve only used validation for enforcing it. Rails “validates_uniqueness_of” Case Sensitivity

3

I use validates_uniqueness_of or the related validates uniqueness: { case_sensitive: false }, but that alone isn’t an assurance that there won’t be duplicates, because of the natural race condition.

In the past in postgres, I’ve used a unique index on LOWER(field), but that isn’t handled properly in the schema.rb. This turns out to be a nice solution.

4

I would use an expression index instead (e.g. lower(column) text_pattern_ops), since CITEXT requires the citext extension to be enabled and cannot use an index with a LIKE prefix query.

See also: the db_text_search gem for ways to do case insensitivity in different databases.

2 Likes