Postgresql – Force exact match from postgres ts_query

full-text-searchpostgresql

I've constructed a column of stem words called journal_tsv from the column journal_name. I've then created a GIN index on journal_tsv. Here's and example of the type of query I'm running:

SELECT DISTINCT(journal_name) FROM my_table WHERE journal_tsv @@ to_tsquery('SCIENTOMETRICS');

This returns a list of 15 journal names

                            journal_name                                                                
--------------------------------------------------------------------------------------------------------------------------------------------
 ISSI 2005: Proceedings of the 10th International Conference of the International Society for Scientometrics and Informetrics, Vols 1 and 2
 SCIENTOMETRICS
 PROCEEDINGS OF ISSI 2009 - 12TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOL 1
 EVALUATION OF RESEARCH BY SCIENTOMETRIC INDICATORS
 8TH INTERNATIONAL CONFERENCE ON SCIENTOMETRICS AND INFORMETRICS, VOLS 1 AND 2 - ISSI-2001, PROCEEDINGS
 ISSI 2005: PROCEEDINGS OF THE 10TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOLS 1 AND 2
 JOURNAL OF SCIENTOMETRIC RESEARCH
 PROCEEDINGS OF ISSI 2009 - 12TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOL 2
 PROCEEDINGS OF ISSI 2011: THE 13TH CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOLS 1 AND 2
 PROCEEDINGS OF ISSI 2015 ISTANBUL: 15TH INTERNATIONAL SOCIETY OF SCIENTOMETRICS AND INFORMETRICS CONFERENCE
 COLLNET JOURNAL OF SCIENTOMETRICS AND INFORMATION MANAGEMENT
 SEVENTH CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, PROCEEDINGS - 1999
 Proceedings of ISSI 2007: 11th International Conference of the International Society for Scientometrics and Informetrics, Vols I and II
 PROCEEDINGS OF ISSI 2007: 11TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOLS I AND II
 14TH INTERNATIONAL SOCIETY OF SCIENTOMETRICS AND INFORMETRICS CONFERENCE (ISSI)
(15 rows)

In most circumstances this works really well but what I can't figure out is how to restrict the results to exact matches? Most of the time fuzzy searches are exactly what I need but how can I use the same type of query to only return exact matches?

I'm trying to get the results I would get from this query:

SELECT DISTINCT(journal_name) FROM my_table WHERE journal_name = 'SCIENTOMETRICS';

But with the performance gains from using tsvector.

                                version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

Best Answer

As filiprem says, you can combine the match and the equality by an AND. It will use the FTS index to get candidates and then just filter out the ones that it needs to for the equality

But you can also build a regular index on 'journal_name' and then just write the query naturally. The 2nd index will take some maintenance overhead, but less than the index your already have does. If you do this type of query a lot, you should probably add the extra index.

PROPOSED QUERY

SELECT * FROM test WHERE MATCH(tags)
AGAINST ('"+proj_7" "(invoice report)"' IN BOOLEAN MODE);

YOUR SAMPLE DATA LOADED

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `test` (
    ->   `tags` text NOT NULL,
    ->   FULLTEXT KEY `tags` (`tags`)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_7 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_8 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_10 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test;
+--------------------------------------+
| tags                                 |
+--------------------------------------+
| proj_7 type_invdtl year_15 month_04  |
| proj_8 type_invdtl year_15 month_04  |
| proj_10 type_invdtl year_15 month_04 |
+--------------------------------------+
3 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(invoice report)"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql>

OTHER EXAMPLES

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(type_invdtl year_15)"' IN BOOLEAN MODE);
+--------------------------------------+
| tags                                 |
+--------------------------------------+
| proj_7 type_invdtl year_15 month_04  |
| proj_8 type_invdtl year_15 month_04  |
| proj_10 type_invdtl year_15 month_04 |
+--------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(type_invdt2 year_15)"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdtl" "+proj_7 +year15"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdtl" "+proj_7 +year16"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdt2" "+proj_7 +year16"' IN BOOLEAN MODE);
Empty set (0.00 sec)

mysql>

GIVE IT A TRY !!!

Postgresql – postgres full text search modifies ts_query

If you don't want the polish library you can use the simple dictionary which will keep the y also:

karoly> select to_tsquery('simple', $$'fabry':*A$$);
+--------------+
| to_tsquery   |
|--------------|
| 'fabry':*A   |
+--------------+