Case sensitivity in PostgreSQL

Posted Almost 8 years ago. Visible to the public. Repeats.

PostgreSQL, unlike MySQL, treats strings as case sensitive in all circumstances.

This includes

comparison with = and LIKE
collision detection in unique indexes

Usually this is fine, but some strings (like emails and usernames) should typically be treated as case insensitive.

There are a few workarounds available:

use the citext extension Show archive.org snapshot (not recommended)
use ILIKE instead of LIKE
use Postgres' lower() function
add an index on lower(email)

Probably the best approach is to just convert emails (and usernames) to lowercase within Rails. Popular authentication libraries (like Clearance and Devise) already do this for you.

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

Prevents you from data breaches and liability risks
Upgrade at your own pace
Works with modern Rubies

Related cards:

The Ruby Object Model

In Ruby (almost) everything is an Object. While this enables a lot of powerful features, this concept might be confusing for developers who have been programming in more static languages, such as Java or C#. This card should help understanding t...

Lightning Talk: Coverage based Test Case Prioritization in Ruby on Rails

For my computer science bachelor's thesis I programmed and evaluated a CLI Test Case Prioritization (TCP) tool for makandra. It has been written as a Ruby Gem and was tested and evaluated against one Ruby on Rails project....

The JavaScript Object Model: A deep dive into prototypes and properties

Speaker today is Henning Koch, Head of Development at makandra.

This talk will be in German with English slides.

Introduction

As web developers we work with JavaScript every day, even when our backend code uses anothe...

PSA: "index: true" in Rails migrations does not work as you'd expect

Several Rails migration methods accept index: true as an option to create an index. In some cases (like #add_column), this option is silently discarded. Know what you are doing, or use #add_index instead.

Example

Consider the following ...

Understanding race conditions with duplicate unique keys in Rails

validates_uniqueness_of is not sufficient to ensure the uniqueness of a value. The reason for this is that in production, multiple worker processes can cause race conditions:

Two concurrent requests try to create a user with the same name (a...

MySQL: How to create columns like "bigint" or "longtext" in Rails migrations, and what :limit means for column migrations

Rails understands a :limit options when you create columns in a migration. Its meaning depends on the column type, and sometimes the supplied value.

[The documentation](http://apidock.com/rails/ActiveRecord/ConnectionAdapters/TableDefinition/co...

How to turn images into inline attachments in emails

Not all email clients support external images in all situations, e.g. an image within a link. In some cases, a viable workaround is to turn your images into inline attachments.

Note

Rails provides a simple mechanism to achieve this:

[h...

CarrierWave: Processing images with libvips

When you write your next CarrierWave uploader, consider processing your images with libvips instead of ImageMagick.

Reasons for libvips

There are several **upsi...

Fixing flaky E2E tests

An end-to-end test (E2E test) is a script that remote-controls a web browser with tools like Selenium WebDriver. This card shows basic techniques for fixing a flaky E2E test suite that sometimes passes and sometimes fails.

Although many examples ...

Integrating or upgrading makandra-rubocop

Introduction

Most of the time it is a tedious task to apply a code style guide to an existing code base as there are likely to be a lot of conflicts. At makandra we are using [makandra-rubocop](https://makandracards.com/makandra/62188-makandr...

Posted by Tobias Kraze to makandra dev (2016-08-01 10:06)