PostgreSQL full text search abbreviations

Question

I created a Postgresql full text search using 'german'. How can I configer, that when I search for "Bezirk", lines containing "Bez." are also a match? (And vice-versa)

You need to create a custom [synonym dictionary](http://www.postgresql.org/docs/current/static/textsearch-dictionaries.html#TEXTSEARCH-SYNONYM-DICTIONARY) if you want to match it with text search — pozs, Mar 05 '16 at 16:00
@pozs seems like your suggestion is the way to go. When you formulate it as an answer I would accept it. — JohnDoe, Mar 05 '16 at 18:54

Artur · Accepted Answer · 2020-02-06T01:06:43.110

3

@pozs is right. You need to use a synonym dictionary.

1 - In the directory $SHAREDIR/tsearch_data create the file german.syn with the following contents:

Bez Bezirk

2 - Execute the query:

CREATE TEXT SEARCH DICTIONARY german_syn (
    template = synonym,
    synonyms = german);
CREATE TEXT SEARCH CONFIGURATION german_syn(COPY='simple');
ALTER TEXT SEARCH CONFIGURATION german_syn
    ALTER MAPPING FOR asciiword, asciihword, hword_asciipart,
        word, hword, hword_part
    WITH german_syn, german_stem;

Now you can test it. Execute queries:

test=# SELECT to_tsvector('german_syn', 'Bezirk') @@ to_tsquery('german_syn', 'Bezirk & Bez');
 ?column? 
----------
 t
(1 row)

test=# SELECT to_tsvector('german_syn', 'Bez Bez.') @@ to_tsquery('german_syn', 'Bezirk');
 ?column? 
----------
 t
(1 row)

Additional links:

~~PostgreSQL: A Full Text Search engine~~ (expired)

edited Feb 06 '20 at 01:06

answered Mar 16 '16 at 14:16

Artur

628
5
15

Hi @Artur, how to use the COPY, is a full language copy? `german` and `simple` are languages.... PS: the link is not working – Peter Krauss Feb 05 '20 at 18:59
@PeterKrauss, can you show en example of your COPY query? PS: thanks, I marked the link as expired. – Artur Feb 06 '20 at 01:08
Hi, thanks the reply! See https://stackoverflow.com/q/60082663/287948 – Peter Krauss Feb 06 '20 at 02:14

score 0 · Answer 2 · answered Mar 04 '16 at 21:35

0

Try using a wildcard in your search.

For example:

tableName.column LIKE 'Bez%'

The % will search for any letter or number after the Bez

answered Mar 04 '16 at 21:35

GIS Student

89
1
2
9

The point is that "Bez." is stored in the database and I query for "Bezirk" so queries are the other way round than you assume and suggest. – JohnDoe Mar 05 '16 at 18:52
The description of the problem is very vague and therefore it is difficult to determine exactly what you are trying to do. Sorry I was not able to assist you. Good luck with your issue. – GIS Student Mar 11 '16 at 20:38

score 0 · Answer 3 · answered Mar 04 '16 at 21:48

Description is very vague to understand what you are trying to achieve, but it looks like you need simple pattern matching search as you looking for abbreviations (so need to do stemming like in Full Text Search). I would with pg_trgm for this purpose:

WITH t(word) AS ( VALUES
  ('Bez'),
  ('Bezi'),
  ('Bezir')
)
SELECT word, similarity(word, 'Bezirk') AS similarity
FROM t
WHERE word % 'Bezirk'
ORDER BY similarity DESC;

Result:

 word  | similarity 
-------+------------
 Bezir |      0.625
 Bezi  |        0.5
 Bez   |      0.375
(3 rows)

PostgreSQL full text search abbreviations

3 Answers3

Linked