HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Force exact match from postgres ts_query

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
exactforcets_querypostgresmatchfrom

Problem

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 INTERNAT

Solution

Another solution is to use a simple text search dictionary rather than a stemming dictionary. Then only exact word matches will be found.

Context

StackExchange Database Administrators Q#234978, answer score: 2

Revisions (0)

No revisions yet.