¿Cómo crear una búsqueda difusa simple solo con PostgreSQL?

Solución:

Postgres proporciona un módulo con varias funciones de comparación de cadenas, como soundex y metaphone. Pero querrá usar la función de edición de distancia de levenshtein.

Example:

test=# SELECT levenshtein('GUMBO', 'GAMBOL');
 levenshtein
-------------
           2
(1 row)

los 2 es la distancia de edición entre las dos palabras. Cuando aplica esto contra varias palabras y ordena por el resultado de la distancia de edición, tendrá el tipo de coincidencias difusas que está buscando.

Pruebe este ejemplo de consulta: (con sus propios nombres de objeto y datos, por supuesto)

SELECT * 
FROM some_table
WHERE levenshtein(code, 'AB123-lHdfj') <= 3
ORDER BY levenshtein(code, 'AB123-lHdfj')
LIMIT 10

Esta consulta dice:

Dame los 10 resultados principales de todos los datos de some_table donde la distancia de edición entre el valor del código y la entrada ‘AB123-lHdfj’ es menor que 3. Obtendrás todas las filas donde el valor del código está dentro de los 3 caracteres de diferencia con ‘ AB123-lHdfj ‘…

Nota: si recibe un error como:

function levenshtein(character varying, unknown) does not exist

Instala el fuzzystrmatch extensión usando:

test=# CREATE EXTENSION fuzzystrmatch;

Paul te habló de levenshtein(). Es una herramienta muy útil, pero también muy lenta con tablas grandes. Tiene que calcular la distancia de Levenshtein desde el término de búsqueda para cada fila. Eso es caro y no se puede utilizar un índice. La variante “acelerada” levenshtein_less_equal() es más rápido para cadenas largas, pero sigue siendo lento sin soporte de índice.

Si sus requisitos son tan sencillo como sugiere el ejemplo, aún puede usar LIKE. Simplemente reemplace cualquier - en su término de búsqueda con % en el WHERE cláusula. Entonces en lugar de:

WHERE code ILIKE '%AB-123-lHdfj%'

Usar:

WHERE code ILIKE '%AB%123%lHdfj%'

O, dinámicamente:

WHERE code ILIKE '%' || replace('AB-123-lHdfj', '-', '%') || '%'

% en LIKE patrones significa 0-n caracteres. O usar _ para exactamente un carácter. O use expresiones regulares para una coincidencia más inteligente:

WHERE code ~* 'AB.?123.?lHdfj'

.? … 0 o 1 caracteres

O:

WHERE code ~* 'AB-?123-?lHdfj'

-? … 0 o 1 guiones

Es posible que desee escapar de los caracteres especiales en LIKE o patrones de expresiones regulares. Ver:

• Función de escape para expresiones regulares o patrones LIKE

---

Si su problema real es mas complejo y necesita algo más rápido, hay varias opciones, según sus requisitos:

• Hay búsqueda de texto completo, por supuesto. Pero esto puede ser una exageración en su caso.

• Un candidato más probable es la coincidencia de trigram con el módulo adicional pg_trgm. Ver:

  • ¿Usar la función de Levenshtein en cada elemento de un tsvector?
  • Variaciones de rendimiento de consultas de PostgreSQL LIKE
  • Publicación de blog relacionada de Depesz

  Se puede combinar con LIKE, ILIKE, ~, o ~* desde PostgreSQL 9.1.
  También interesante en este contexto: el similarity() función o % operador de ese módulo.

• Por último, pero no menos importante, puede implementar una solución tejida a mano con una función para normalizar las cadenas que se van a buscar. Por ejemplo, podrías transformar AB1-23-lHdfj -> ab123lhdfj, guárdelo en una columna adicional y busque con términos transformados de la misma manera.

  O use un índice en la expresión en lugar de la columna redundante. (Las funciones involucradas deben ser IMMUTABLE.) Posiblemente combine eso con pg_tgrm desde arriba.

Descripción general de las técnicas de coincidencia de patrones:

• Coincidencia de patrones con LIKE, SIMILAR TO o expresiones regulares en PostgreSQL

---

---

Utiliza Nuestro Buscador