Postgresql – Find partial match with textsearch query

full-text-searchlikepattern matchingpostgresqlquery

I have the following query that I did hard looking on this site:

SELECT *
FROM   renns
WHERE
    (
        to_tsvector('english', coalesce(numar_admin, '')) || 
        to_tsvector('english', coalesce(nume_strada, '')) || 
        to_tsvector('english', coalesce(nume_prenume, ''))|| 
        to_tsvector('english', coalesce(bloc, '')) ||
        to_tsvector('english', coalesce(apartament, '')) 
    ) @@ plainto_tsquery('english', '$q')

It works if I write in the variable $q all the text that appears in one of the columns present in the query. For example: cherry

I would like to write only: cher and return all rows fields in which it finds this string: "cher".

Best Answer

Postgres text search supports prefix matching:

SELECT *
FROM   renns
WHERE (
   to_tsvector('english', coalesce(numar_admin, '')) || 
   to_tsvector('english', coalesce(nume_strada, '')) || 
   to_tsvector('english', coalesce(nume_prenume, ''))|| 
   to_tsvector('english', coalesce(bloc, '')) ||
   to_tsvector('english', coalesce(apartament, '')) 
) @@ to_tsquery('simple', $q || ':*');

($q being a single word.)
This can use an index on your expression:

(to_tsvector('english', coalesce(numar_admin , '')) || 
 to_tsvector('english', coalesce(nume_strada , '')) || 
 to_tsvector('english', coalesce(nume_prenume, '')) || 
 to_tsvector('english', coalesce(bloc        , '')) ||
 to_tsvector('english', coalesce(apartament  , '')))

Using the 'simple' dictionary disables stemming. If you want stemming, use the 'english' dictionary like you had before.

"caisului 498:*" the "CAISULUI" string is from one column and the number from another column of the renns table

To pass multiple lexemes, apply 'english' stemming and do prefix matching for all of them:

@@ (to_tsquery('english', column_a || ':*') &&
    to_tsquery('english', column_b || ':*') -- more?   
   )

Detailed explanation:

Get partial match from GIN indexed TSVECTOR column

Prefix matching matches at the start of lexemes. For any partial match, you can use LIKE or regular expression matches (~) in combination with a trigram index. Different approach! Requires a different query and a different index! See:

There are smart ways to integrate both. The manual has an example.

Related Solutions

Postgresql – Parse hyphenated word to unsigned integer

I think it would be better to work around your problem: simply replace hyphens with spaces in the strings to be ts_vectored. If necessary, you can store the unhyphenated values in a separate column and run your searches against it.

PostgreSQL – Pattern Match Against Array Elements

You can accomplish what you're asking for by doing something like this.

I created a table and data to help better illustrate what I'm doing.

CREATE TABLE logs (id serial NOT NULL PRIMARY KEY, lognum int, flightnum int);

INSERT INTO logs (lognum, flightnum) VALUES (1,6);
INSERT INTO logs (lognum, flightnum) VALUES (1,7);
INSERT INTO logs (lognum, flightnum) VALUES (1,8);
INSERT INTO logs (lognum, flightnum) VALUES (2,80);
INSERT INTO logs (lognum, flightnum) VALUES (3,12);
INSERT INTO logs (lognum, flightnum) VALUES (4,8008);

postgres@[local]:5432:postgres:=# SELECT * FROM logs;
 id | lognum | flightnum 
----+--------+-----------
 13 |      1 |         6
 14 |      1 |         7
 15 |      1 |         8
 16 |      2 |        80
 17 |      3 |        12
 18 |      4 |      8008
(6 rows)

Time: 0.188 ms
postgres@[local]:5432:postgres:=#

Note that 1, 2, and 4 have flights with 8, 80, and 8008.

Now, using this query, find the lognums with 8 as a flightnum. This takes your original query with an array_agg, wraps another query around it to generates subscripts for each member in the array, which can be arbitrarily large. Finally, an outer query wraps that, which uses the generated subscript to allow you to do a comparison against each of the members of the flightnums array to see if they are LIKE '8%'.

SELECT lognum FROM ( 
                      SELECT lognum, 
                      flightnums, 
                      generate_subscripts(flightnums, 1) AS s 
                      FROM ( 
                             SELECT lognum, 
                                    array_agg(flightnum) AS flightnums 
                                    FROM logs 
                                         GROUP BY lognum
                           ) AS t1
                   ) AS t2 
            WHERE flightnums[s]::text LIKE '8%' ORDER BY lognum;

Which gives you the following output

postgres@[local]:5432:postgres:=# SELECT lognum FROM (SELECT lognum, flightnums, generate_subscripts(flightnums, 1) AS s FROM ( SELECT lognum, array_agg(flightnum) AS flightnums FROM logs GROUP BY lognum) AS t1) AS t2 WHERE flightnums[s]::text LIKE '8%' ORDER BY lognum;
 lognum 
--------
      1
      2
      4
(3 rows)

Time: 0.338 ms
postgres@[local]:5432:postgres:=#

As would be expected from the data above.

For further array manipulation needs, I would recommend reading up on their chapter in the PostgreSQL documentation arrays

Best Answer

Related Solutions

Postgresql – Parse hyphenated word to unsigned integer

PostgreSQL – Pattern Match Against Array Elements

Related Question