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.<\/p>\n
Ya sea por c\u00f3digo o por herramientas clientes para manipular base de datos como por ejemplo pgAdmin, al utilizar la opci\u00f3n de \u00abcrear \u00edndice \u00fanico\u00bb en alg\u00fan campo de una tabla es muy sencillo. Obviamos probar y continuamos elaborando las tablas pero se presenta un inconveniente mucho despu\u00e9s al momento de insertar datos.<\/p>\n
Suponiendo que tenemos una tabla de \u00abusuarios\u00bb cuyo campo nombre_usuario es \u00fanico pero nos damos cuenta que al insertar un usuario \u00abAdministrador\u00bb y luego otro \u00abadministrador\u00bb, el campo lo permite, esto es debido a que los reconoce como distintos por la primera letra en may\u00fascula y min\u00fascula, el campo es case-sensitive.<\/p>\n
Para solucionar esto, en vez de realizar la creaci\u00f3n del \u00edndice \u00fanico de la manera com\u00fan:<\/p>\n
CREATE UNIQUE INDEX usuarios_nombre_usuario_index ON usuarios USING btree (nombre_usuario);<\/pre>\n<\/p>\n
Usamos una versi\u00f3n modificada para que se cree todos los \u00edndices en min\u00fascula LOWER:<\/p>\n
CREATE UNIQUE INDEX usuarios_ nombre_usuario_index ON usuarios USING btree (lower((nombre_usuario)::text));<\/pre>\n<\/p>\n
Lo que hicimos fue, guardar en el index todo en min\u00fascula para que al momento de insertar usuarios, se pueda comprar y exista la restricci\u00f3n, convirtiendo dicho \u00edndice \u00fanico en case-insensitive. Tambi\u00e9n se podr\u00eda haber colocado todo en may\u00fasculas. La conversi\u00f3n a string ::text solo es necesaria cuando se insertar datos num\u00e9ricos, ya que lower requiere solo strings, por ejemplo usar un usuario \u00ab1234\u00bb, que no es el caso pero lo tomo en cuenta en otros campos.<\/p>\n
<\/h3>\n
B\u00fasquedas<\/h3>\n
Para las b\u00fasquedas ocurre lo mismo, los campos por defecto son case-sensitive y diferencian de may\u00fasculas y min\u00fasculas. Si tenemos \u00abAdministrador\u00bb guardado y buscamos \u00abWHERE nombre_usuario = ‘administrador’ \u00ab, no nos devolver\u00eda nada, no lo encontrar\u00eda.<\/p>\n
Para esto podemos realizar varias soluciones.<\/p>\n
La primera, se podr\u00eda crear una tabla con un collation que no sea case-sensitive y esto afectar\u00eda a todos los campos creados en esa tabla.<\/p>\n
La segunda creando el \u00edndice con el modulo citext, que debes verificar si viene con la versi\u00f3n de postgres y si est\u00e1 activado, entonces se usar\u00eda en el campo al momento de crear el \u00edndice \u00fanico<\/p>\n
CREATE INDEX ON usuarios (nombre_usuario::citext);<\/pre>\n<\/p>\n
La tercera y sencilla forma seria utilizar la palabra clave \u00abILIKE\u00bb, que ser\u00eda un LIKE como el de otros manejadores de base de datos, esto har\u00eda que la consulta fuera case-insensitive y si devolver\u00eda el resultado.<\/p>\n
SELECT * FROM usuarios WHERE nombre_usuario ILIKE 'administrador';<\/pre>\n<\/p>\n
Toma en cuenta que ILIKE es una opci\u00f3n que no se encuentra en el est\u00e1ndar de SQL pero es una extensi\u00f3n propia de postgres.<\/p>\n
Cuarta forma, es muy com\u00fan, sino deseamos usar ilike y queremos que sea m\u00e1s exacta la b\u00fasqueda, evitar comodines %%, podemos colocar los campos en min\u00fasculas antes de realizar la b\u00fasqueda.<\/p>\n
SELECT * FROM usuarios WHERE LOWER(nombre_usuario) = LOWER('administrador');<\/pre>\n<\/p>\n
Toma en cuenta que al utilizar la funci\u00f3n lower sobre ese campo, se obvia los \u00edndices y se realiza un b\u00fasqueda 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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. \u00cdndices \u00danicos Ya sea por c\u00f3digo o por herramientas clientes para manipular base de datos como por ejemplo pgAdmin, al […]<\/p>\n","protected":false},"author":1,"featured_media":720,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[264],"tags":[268,266,269,265,267],"_links":{"self":[{"href":"https:\/\/gregorgonzalez.com.ve\/blog\/wp-json\/wp\/v2\/posts\/716"}],"collection":[{"href":"https:\/\/gregorgonzalez.com.ve\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gregorgonzalez.com.ve\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gregorgonzalez.com.ve\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/gregorgonzalez.com.ve\/blog\/wp-json\/wp\/v2\/comments?post=716"}],"version-history":[{"count":4,"href":"https:\/\/gregorgonzalez.com.ve\/blog\/wp-json\/wp\/v2\/posts\/716\/revisions"}],"predecessor-version":[{"id":722,"href":"https:\/\/gregorgonzalez.com.ve\/blog\/wp-json\/wp\/v2\/posts\/716\/revisions\/722"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/gregorgonzalez.com.ve\/blog\/wp-json\/wp\/v2\/media\/720"}],"wp:attachment":[{"href":"https:\/\/gregorgonzalez.com.ve\/blog\/wp-json\/wp\/v2\/media?parent=716"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gregorgonzalez.com.ve\/blog\/wp-json\/wp\/v2\/categories?post=716"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gregorgonzalez.com.ve\/blog\/wp-json\/wp\/v2\/tags?post=716"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}