Índices Únicos y Búsquedas case-insensitive en Postgresql

Cada vez que trabajo en postgres me tropiezo con los mismos inconvenientes. Entonces se puede decir que hay una serie de reglas que no debo olvidar. Hay dos casos en particular que tomar en cuenta.

Índices Únicos

Ya sea por código o por herramientas clientes para manipular base de datos como por ejemplo pgAdmin, al utilizar la opción de «crear índice único» en algún campo de una tabla es muy sencillo. Obviamos probar y continuamos elaborando las tablas pero se presenta un inconveniente mucho después al momento de insertar datos.

Suponiendo que tenemos una tabla de «usuarios» cuyo campo nombre_usuario es único pero nos damos cuenta que al insertar un usuario «Administrador» y luego otro «administrador», el campo lo permite, esto es debido a que los reconoce como distintos por la primera letra en mayúscula y minúscula, el campo es case-sensitive.

Para solucionar esto, en vez de realizar la creación del índice único de la manera común:

CREATE UNIQUE INDEX usuarios_nombre_usuario_index ON usuarios USING btree (nombre_usuario);

1	CREATE UNIQUE INDEX usuarios_nombre_usuario_index ON usuarios USING btree (nombre_usuario);

Usamos una versión modificada para que se cree todos los índices en minúscula LOWER:

CREATE UNIQUE INDEX usuarios_ nombre_usuario_index ON usuarios USING btree (lower((nombre_usuario)::text));

1	CREATE UNIQUE INDEX usuarios_ nombre_usuario_index ON usuarios USING btree (lower((nombre_usuario)::text));

Lo que hicimos fue, guardar en el index todo en minúscula para que al momento de insertar usuarios, se pueda comprar y exista la restricción, convirtiendo dicho índice único en case-insensitive. También se podría haber colocado todo en mayúsculas. La conversión a string ::text solo es necesaria cuando se insertar datos numéricos, ya que lower requiere solo strings, por ejemplo usar un usuario «1234», que no es el caso pero lo tomo en cuenta en otros campos.

Búsquedas

Para las búsquedas ocurre lo mismo, los campos por defecto son case-sensitive y diferencian de mayúsculas y minúsculas. Si tenemos «Administrador» guardado y buscamos «WHERE nombre_usuario = ‘administrador’ «, no nos devolvería nada, no lo encontraría.

Para esto podemos realizar varias soluciones.

La primera, se podría crear una tabla con un collation que no sea case-sensitive y esto afectaría a todos los campos creados en esa tabla.

La segunda creando el índice con el modulo citext, que debes verificar si viene con la versión de postgres y si está activado, entonces se usaría en el campo al momento de crear el índice único

CREATE INDEX ON usuarios (nombre_usuario::citext);

1	CREATE INDEX ON usuarios (nombre_usuario::citext);

La tercera y sencilla forma seria utilizar la palabra clave «ILIKE», que sería un LIKE como el de otros manejadores de base de datos, esto haría que la consulta fuera case-insensitive y si devolvería el resultado.

SELECT * FROM usuarios WHERE nombre_usuario ILIKE 'administrador';

1	SELECT * FROM usuarios WHERE nombre_usuario ILIKE 'administrador';

Toma en cuenta que ILIKE es una opción que no se encuentra en el estándar de SQL pero es una extensión propia de postgres.

Cuarta forma, es muy común, sino deseamos usar ilike y queremos que sea más exacta la búsqueda, evitar comodines %%, podemos colocar los campos en minúsculas antes de realizar la búsqueda.

SELECT * FROM usuarios WHERE LOWER(nombre_usuario) = LOWER('administrador');

1	SELECT * FROM usuarios WHERE LOWER(nombre_usuario) = LOWER('administrador');

Toma en cuenta que al utilizar la función lower sobre ese campo, se obvia los índices y se realiza un búsqueda secuencial que puede ser muy lenta para grades registros, hablando de millones de registros, para mejor el performance en grandes tablas, es mejor intentar con el collation y con citext.