Solución:
Sugiero esta expresión para consulta e índice:
SELECT * FROM tbl
WHERE to_tsvector('simple', f_concat_ws(' ', country, city, street, house_nr, postcode))
@@ plainto_tsquery('simple', '22 Kärntner Wien');
Tenga en cuenta la función personalizada f_concat_ws() encima. Eso es porque concat_ws() es solo STABLE no IMMUTABLE. Necesitas crearlo primero:
CREATE OR REPLACE FUNCTION f_concat_ws(text, VARIADIC text[])
RETURNS text LANGUAGE sql IMMUTABLE AS 'SELECT array_to_string($2, $1)';
Se puede utilizar como reemplazo directo de concat_ws(), excepto que solo acepta datos de texto reales como entrada (lo que nos permite hacer que IMMUTABLE sin hacer trampa, efectivamente). Explicación detallada (¡léelo!):
- Combinar dos columnas y agregarlas en una nueva columna
Sobre VARIADIC:
- Pasando varios valores en un solo parámetro
Para muchos columnas, esto es más corto y más rápido. usted podría prescindir de él, pero luego la sintaxis se vuelve bastante detallada (ver la respuesta de joanolo).
El índice coincidente para ir con esto:
CREATE INDEX tbl_adr_fts_idx ON tbl USING GIN (
to_tsvector('simple', f_concat_ws(' ', country, city, street, house_nr, postcode)));
Está tratando con datos de direcciones internacionales, así que hágalo no utilizar el english configuración de búsqueda de texto. La derivación tiene poco sentido para nombres y la mayoría de los datos de su ejemplo ni siquiera son en inglés para empezar. Utilizar el simple configuración en su lugar. Necesita el formulario con dos parámetros; consulte a continuación.
Concatenar las cadenas y llamar a la función más cara to_tsvector() una vez. Usar concat_ws() para hacer frente a los posibles valores NULL con elegancia. Más barato en general y también más corto.
- Reemplazo de cadenas usando cadenas concatenadas de varias columnas
Como comenté, la búsqueda de texto completo tiene soporte limitado para la concordancia aproximada, pero existe la característica de la concordancia de prefijos que a menudo se pasa por alto:
- Obtenga una coincidencia parcial de la columna TSVECTOR indexada por GIN
Entonces, si no está seguro de si es ‘Kärntner’ o ‘Kärnten’y si es ‘Straße’, ‘strasse’ o ‘Strabe’ (como en sus datos de ejemplo con errores) pero sabe que la segunda palabra sigue a la primera, podría:
... @@ to_tsquery('simple', '22 & Kärnt:* <-> Stra:* & Wien')<-> es el operador de búsqueda de frases y requiere Postgres 9,6.
- ¿Cómo almacenar historias cortas para acceder a frases individuales?
Y si también quieres ignorar los signos diacríticos (‘a’ <> ‘a’), agregar unaccent() a la mezcla. Puedes usarlo como separado función o puedes agregarlo como diccionario a su configuración de búsqueda de texto. Primero debes instalar la extensión …
- ¿PostgreSQL admite intercalaciones “insensibles al acento”?
Descripción general de la opción de coincidencia de patrones en instalaciones típicas de Postgres:
- Coincidencia de patrones con LIKE, SIMILAR TO o expresiones regulares en PostgreSQL
Joanolo ya proporcionó información básica sobre FTS y el enlace al manual para obtener más información.
Abordar su comentario
Estoy intentando agregar este índice pero me da un error:
ERROR: functions in index expression must be marked IMMUTABLE
Hay dos variantes de la función to_tsvector() – ver “sobrecarga de funciones”. El primero toma solo text, la segunda toma regconfig y text. Ver por ti mismo:
SELECT proname, provolatile, proargtypes[0]::regtype, proargtypes[1]::regtype
FROM pg_proc
WHERE proname="to_tsvector";
Solo el segundo es IMMUTABLE y se puede utilizar directamente en una expresión de índice. ‘sencillo’ en el ejemplo anterior es una configuración de búsqueda de texto (regconfig).
Más importante, mi descuido: concat_ws() (que tenía en mi primera versión) es solo STABLE, no IMMUTABLE. Agregué los pasos necesarios arriba.
Relacionado:
-
¿Cómo afectan las palabras clave INMUTABLE, STABLE y VOLATILE al comportamiento de la función?
-
Combinar dos columnas y agregarlas en una nueva columna
Imaginemos que esta es su tabla y algunos datos:
CREATE TABLE t
(
country text,
city text,
street text,
house_number text,
post_code text
) ;
INSERT INTO t
VALUES
('Österreich', 'Vienna', 'HauptStrasse', '123', '12345'),
('France', 'Paris', 'Rue du Midi', '12A', '01234'),
('España', 'Barcelona', 'Passeig de Gràcia', '32', '08001'),
('United Kingdom', 'London', 'Oxford Street', '20', 'W1D 1AS'),
('Nederland', 'Amsterdam', 'Leidsekruisstraat', '6-8', '1017 RH') ;
[NOTE: check it at http://rextester.com/DOJN8533]
La forma de realizar una búsqueda de texto completo en varias columnas usando PostgreSQL (asumiendo que ‘inglés’ es el nombre de su configuración de FTS), es usando una consulta como:
SELECT
*
FROM
t
WHERE
(
to_tsvector('english', coalesce(country, '')) ||
to_tsvector('english', coalesce(city, '')) ||
to_tsvector('english', coalesce(street, '')) ||
to_tsvector('english', coalesce(house_number, '')) ||
to_tsvector('english', coalesce(post_code, ''))
) @@ plainto_tsquery('english', 'Amsterdam') ;
Donde clasuse significa:
(this tsvector = document) @@ /* matches */ (this tsquery = query)
A tsvector es un tipo de datos especial utilizado por PostgreSQL para almacenar datos transformados (por ejemplo, todos en minúsculas, con comas eliminadas, con palabras identificadas y enumeradas, etc.) sobre un texto. A tsquery es una forma de pregunta por características de un documento (por ejemplo que contiene esto _y_ eso).
los || operador combina tsvectors (digamos que “los suma”).
Si desea acelerar las cosas, debe tener un índice funcional, definido como:
CREATE INDEX ts_idx
ON t USING gist (
(
to_tsvector('english', coalesce(country, '')) ||
to_tsvector('english', coalesce(city, '')) ||
to_tsvector('english', coalesce(street, '')) ||
to_tsvector('english', coalesce(house_number, '')) ||
to_tsvector('english', coalesce(post_code, ''))
)
) ;
Debe comprobar detenidamente la documentación sobre la búsqueda de texto completo. Es un poco intimidante, porque hay muchas posibilidades, pero vale la pena dedicarle tiempo.
Para ordenar los resultados cuando hay muchos, debe usar él ts_rank función para ORDER BY y luego limitar.