Usando campos case-insensitive no PostgreSQL

Faz algum tempo que venho usando o PostgreSQL em vez de MySQL, por diversos motivos; o principal deles é que o PostgreSQL é muito melhor. Mas nem tudo funciona exatamente da mesma forma que o MySQL. Uma das principais mudanças é que campos de texto são case-sensitive.

Para demonstrar o modo como o PostgreSQL funciona temos que criar uma tabela e índice único.

# create the table
CREATE TABLE users (name text NOT NULL, username text NOT NULL);

# create a unique index
CREATE UNIQUE INDEX unique_username_on_users ON users (username);

Ao inserir alguns registros, perceba que o PostgreSQL continua aceitando “termos duplicados”.

INSERT INTO users (name, username) VALUES ('John Doe', 'john');
INSERT INTO users (name, username) VALUES ('Doe, John', 'JOHN');

Se selecionarmos os registros com a query SELECT * FROM users, verá que foram adicionados corretamente.

-----------+----------
   name    | username
-----------+----------
 John Doe  | john
 Doe, John | JOHN
-----------+----------
(2 rows)

O índice unique_username_on_users só será usado em termos que seguem exatamente a mesma sequência de maiúsculas e minúsculas.

INSERT INTO users (name, username) VALUES ('Doe, John', 'JOHN');
# ERROR:  duplicate key value violates unique constraint "unique_username_on_users"
# DETAIL:  Key (username)=(JOHN) already exists.

Se você vem do MySQL, provavelmente não é o que você queria. Felizmente o PostgreSQL permite criar índices que são definidos à partir de expressões. Nosso índice, com uma ligeira mudança, trará um resultado semelhante ao MySQL.

# remove all records
DELETE FROM users;

# remove index
DROP INDEX unique_username_on_users;

# create new index
CREATE UNIQUE INDEX unique_username_on_users ON users (lower(username));

Agora, ao inserir registros com termos duplicados, mesmo que com casos diferentes, teremos o comportamento esperado. Note que o índice utiliza a expressão lower(username) para fazer a verificação.

INSERT INTO users (name, username) VALUES ('John Doe', 'john');

INSERT INTO users (name, username) VALUES ('Doe, John', 'JOHN');
# ERROR:  duplicate key value violates unique constraint "unique_username_on_users"
# DETAIL:  Key (lower(username))=(john) already exists.

Infelizmente, ainda temos um problema: o campo username continua sendo case-sensitive, o que significa que consultas que usam este campo na cláusula WHERE continuarão sendo ignorados.

SELECT * FROM users WHERE username = 'john';

# ----------+----------
#    name   | username
# ----------+----------
#  John Doe | john
# ----------+----------
# (1 row)

SELECT * FROM users WHERE username = 'jOhN';

# ------+----------
#  name | username
# ------+----------
# (0 rows)

Para fazer com que os registros sejam retornados independente do caso das letras, você pode usar a função lower, mas essa solução paliativa não é prática, muito menos elegante, sem falar que é muito verbosa.

SELECT * FROM users WHERE lower(username) = lower('jOhN');

# ----------+----------
#    name   | username
# ----------+----------
#  John Doe | john
# (1 row)

Não se despere! Existe uma maneira muito mais interessante para resolver este problema. Sempre que você quiser ignorar o caso das letras, utilize a extensão citext.

Extensão citext

A extensão citext permite definir campos case-insensitive sem precisar fazer nenhuma das peripécias acima. Na verdade, o que esta extensão faz é definir a chamada para lower automaticamente e esta é a única diferença em relação a um campo do tipo text.

Para ativar o citext, basta executar a query abaixo.

CREATE EXTENSION citext;

Para listar todas as extensões ativas em seu sistema, execute o comando \dx.

# \dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description
---------+---------+------------+--------------------------------------------------
 citext  | 1.0     | public     | data type for case-insensitive character strings
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

Agora basta utilizar o tipo citext.

# alter username column type
ALTER TABLE users ALTER COLUMN username TYPE citext;

# remove index
DROP INDEX unique_username_on_users;

# recreate index without the lower call
CREATE UNIQUE INDEX unique_username_on_users ON users (username);

Como você pode ver, tudo está funcionando como esperado.

SELECT * FROM users WHERE username = 'jOhN';

# ----------+----------
#    name   | username
# ----------+----------
#  John Doe | john
# (1 row)

INSERT INTO users (name, username) VALUES ('JD', 'jOhN');
# ERROR:  duplicate key value violates unique constraint "unique_username_on_users"
# DETAIL:  Key (username)=(jOhN) already exists.

Usando citext no Rails

O Rails suporta o tipo citext nativamente à partir da versão 4.2.0. Apenas lembre-se de ativar a extensão citext no PostgreSQL com o método enable_extension.

class CreateUsers < ActiveRecord::Migration
  def change
    enable_extension "citext"

    create_table :users do |t|
      t.text :name, null: false
      t.citext :username, null: false
    end

    add_index :users, :username, unique: true
  end
end

Pronto! Agora o campo username é case-insensitive e você não precisa mais fazer nenhuma chamada para o método lower manualmente.

Se você usa uma versão mais antiga do Rails, não deixe de olhar a extensão activerecord-postgresql-citext. Adicione a extensão ao seu arquivo Gemfile.

source "https://rubygems.org"
gem "rails", "4.1.9"
gem "pg"
gem "activerecord-postgresql-citext"

Depois de executar bundle install, pode usar o tipo citext em suas migrations, assim como no exemplo anterior.

Finalizando

Nem todo software se comporta de maneira igual e, por isso, é muito importante que você conheça as ferramentas que está usando. Assim, não cairá em diferenças como esta que podem trazer inconsistências.

Changelog

• 28/01/2015: Artigo atualizado com informações sobre o suporte nativo do tipo citext no Rails 4.2.0+.