PostgreSQL: Cómo hacer una consulta "insensible a mayúsculas y minúsculas"

Usando ILIKE en lugar de LIKE

SELECT id FROM groups WHERE name ILIKE 'Administrator'

El enfoque más común es ya sea minúscula o mayúscula la cadena de búsqueda y los datos. Pero hay dos problemas con eso.

1. funciona en inglés, pero no en todos los idiomas. (Tal vez ni siquiera en la mayoría de los idiomas.) No todas las letras minúsculas tienen una letra mayúscula; no todas las letras mayúsculas tienen letra minúscula.
2. Usando funciones como lower() y upper() le dará una secuencia escanear. No puede usar índices. En mi sistema de prueba, usando lower() toma aproximadamente 2000 veces más que una consulta que puede usar un índice. (Los datos de prueba tienen un poco más de 100k filas.)

Hay al menos tres soluciones menos utilizadas que podrían ser más efectivas.

1. Utilice el módulo citext , que en su mayoría imita el comportamiento de un tipo de datos que no distingue entre mayúsculas y minúsculas. Una vez cargado ese módulo, puede crear un índice que no distingue entre mayúsculas y minúsculas mediante CREATE INDEX ON groups (name::citext);. (Pero ver más abajo.)
2. Utilice una intercalación que no distingue entre mayúsculas y minúsculas. Esto se establece cuando usted inicializar a base. El uso de una intercalación que no distingue entre mayúsculas y minúsculas significa que puede aceptar casi cualquier formato del código del cliente, y aún así volverás resultados útiles. (También significa que no puede hacer consultas sensibles a mayúsculas y minúsculas. Duh.)
3. Cree un índice funcional. Cree un índice en minúsculas usando CREATE INDEX ON groups (LOWER(name));. Habiendo hecho eso, puedes tomar ventaja del índice con consultas como SELECT id FROM groups WHERE LOWER(name) = LOWER('ADMINISTRATOR');, o SELECT id FROM groups WHERE LOWER(name) = 'administrator'; Tienes que recordar usar LOWER(), sin embargo.

El módulo citext no proporciona un tipo de datos que no distingue entre mayúsculas y minúsculas. En su lugar, se comporta como si cada cadena estuviera en minúsculas. Es decir, se comporta como si hubieras llamado lower() en cada cadena, como en el número 3 anterior. La ventaja es que los programadores no tienen que recordar las cadenas en minúsculas. Pero debe leer las secciones "Comportamiento de comparación de cadenas" y "Limitaciones" en los documentos antes de decidir usar citext.

Puede usar ILIKE. es decir,

SELECT id FROM groups where name ILIKE 'administrator'

También puedes leer la palabra clave ILIKE. Puede ser bastante útil a veces, aunque no se ajusta al estándar SQL. Ver aquí para más información: http://www.postgresql.org/docs/9.2/static/functions-matching.html

También puedes usar expresiones regulares POSIX, como

SELECT id FROM groups where name ~* 'administrator'

SELECT 'asd' ~* 'AsD' devuelve t