-- Load extension CREATE EXTENSION pg_kaasearch; -- 1. Basic Latin tokenization SELECT to_tsvector('kaasearch', 'Qaraqalpaqstan'); to_tsvector -------------------- 'qaraqalpaqstan':1 (1 row) -- 2. Basic Cyrillic tokenization (should produce same result as Latin) SELECT to_tsvector('kaasearch', 'Қарақалпақстан'); to_tsvector -------------------- 'qaraqalpaqstan':1 (1 row) -- 3. Cross-script: both scripts produce identical lexemes SELECT to_tsvector('kaasearch', 'Qaraqalpaqstan') = to_tsvector('kaasearch', 'Қарақалпақстан') AS cross_script_match; cross_script_match -------------------- t (1 row) -- 4. Stop words are filtered out, numerals are kept SELECT to_tsvector('kaasearch', 'ol hám bul, sebebi bir eki'); to_tsvector ----------------------------------- 'bir':5 'eki':6 'ol':1 'sebebi':4 (1 row) -- 5. Mixed text: stop words removed, content words kept SELECT to_tsvector('kaasearch', 'bul Respublika'); to_tsvector ---------------- 'respublika':2 (1 row) -- 6. Stemming: plural suffix -lar stripped SELECT to_tsvector('kaasearch', 'kitaplar'); to_tsvector ------------- 'kitap':1 (1 row) -- 7. Stemming: case suffix -da stripped SELECT to_tsvector('kaasearch', 'qalada'); to_tsvector ------------- 'qala':1 (1 row) -- 8. Stemming: genitive suffix -nıń stripped, vowel restored SELECT to_tsvector('kaasearch', 'xalıqtıń xalqımızdıń'); to_tsvector ------------- 'xalıq':1,2 (1 row) -- 9. Cyrillic stemming: transliterate then stem SELECT to_tsvector('kaasearch', 'китаплар'); to_tsvector ------------- 'kitap':1 (1 row) -- 10. Cross-script stemming match SELECT to_tsvector('kaasearch', 'kitaplar') = to_tsvector('kaasearch', 'китаплар') AS stem_cross_match; stem_cross_match ------------------ t (1 row) -- 11. Query matching: Latin query finds Cyrillic document SELECT to_tsvector('kaasearch', 'Қарақалпақстан Республикасы') @@ to_tsquery('kaasearch', 'respublika') AS query_match; query_match ------------- t (1 row) -- 12. Special transliteration: ъе → ye SELECT to_tsvector('kaasearch', 'объект'); to_tsvector ------------- 'obyekt':1 (1 row) -- 13. Multi-word cross-script search SELECT to_tsvector('kaasearch', 'Қарақалпақ тили') @@ to_tsquery('kaasearch', 'qaraqalpaq & til') AS multi_word_match; multi_word_match ------------------ t (1 row) -- 14. Karakalpak ў (U+045E) and Ў (U+040E) → w SELECT to_tsvector('kaasearch', 'суў СУЎ таўлар'); to_tsvector ------------------- 'suw':1,2 'taw':3 (1 row) SELECT to_tsvector('kaasearch', 'суў') @@ to_tsquery('kaasearch', 'suw') AS w_match; w_match --------- t (1 row) -- 15. Lower case: Í → ı, I → i, Karakalpak capitals (locale independent) SELECT to_tsvector('kaasearch', 'ÍDÍS Ídıs ıdıs'); to_tsvector -------------- 'ıdıs':1,2,3 (1 row) SELECT to_tsvector('kaasearch', 'ÓZBEKSTAN ǴÁREZSIZLIK JAŃA'); to_tsvector ---------------------------------------- 'jańa':3 'ózbekstan':1 'ǵárezsizlik':2 (1 row) SELECT to_tsvector('kaasearch', 'Ídıs') @@ to_tsquery('kaasearch', 'ıdıs') AS dotless_match; dotless_match --------------- t (1 row) -- 16. Numbers and words with digits are indexed SELECT to_tsvector('kaasearch', '1991-jılı 5-mektepke COVID19 2016 3.14 -7'); to_tsvector ------------------------------------------------------------------------ '-7':8 '1991':1 '2016':6 '3.14':7 '5':3 'covid19':5 'jıl':2 'mektep':4 (1 row) SELECT to_tsvector('kaasearch', 'Ǵárezsizlik 1991-jılı') @@ to_tsquery('kaasearch', '1991') AS number_match; number_match -------------- t (1 row) -- 17. Real words that look like suffixes are not stop words SELECT to_tsvector('kaasearch', 'tar kóshe, ter shıqtı'); to_tsvector ------------------------------------ 'kóshe':2 'shıq':4 'tar':1 'ter':3 (1 row) -- 18. Soft sign before е → ye SELECT to_tsvector('kaasearch', 'пьеса'); to_tsvector ------------- 'pyesa':1 (1 row) -- 19. Apostrophe spelling (a' i' g' o' n' u'): the parser joins a letter and its apostrophe, so -- o'zbek is one word, ózbek; all apostrophe signs are read alike; a word in quotation marks -- keeps its last n ('Qazan'); kaa_normalize() gives the same text SELECT to_tsvector('kaasearch', 'O''zbekstan qi''zdi''n'' g''alaba U''YDE A''DEBIYAT'); to_tsvector ------------------------------------------------------ 'qız':2 'ádebiyat':5 'ózbekstan':1 'úy':4 'ǵalaba':3 (1 row) SELECT to_tsvector('kaasearch', 'g''alaba o''zbek') = to_tsvector('kaasearch', 'ǵalaba ózbek') AS apostrophe_match; apostrophe_match ------------------ t (1 row) SELECT to_tsvector('kaasearch', 'so‘z soʻz so’z so`z'); to_tsvector --------------- 'sóz':1,2,3,4 (1 row) SELECT to_tsvector('kaasearch', '''Qazan'' qalası, ten'' huqıq'); to_tsvector -------------------------------------- 'huqıq':4 'qala':2 'qazan':1 'teń':3 (1 row) -- a letter with its apostrophe at the end of a word (bardi' = bardı), and a quotation of several -- words whose closing mark follows n ('Jańa Ózbekstan' stays ózbekstan) SELECT to_tsvector('kaasearch', 'bardi'' kitabi'' xalqi'''); to_tsvector ----------------------------- 'bar':1 'kitap':2 'xalıq':3 (1 row) SELECT to_tsvector('kaasearch', '''Jańa Ózbekstan'' gazetası, ''O''zbekstan'' kitabi'''); to_tsvector ----------------------------------------------- 'gazeta':3 'jańa':1 'kitap':5 'ózbekstan':2,4 (1 row) SELECT kaa_normalize('''Jańa Ózbekstan'' gazetası, bardi'' ''Qazan'''); kaa_normalize ------------------------------------------ 'jańa ózbekstan' gazetası, bardı 'qazan' (1 row) SELECT to_tsvector('kaasearch', kaa_normalize('g''alaba o''zbek')) = to_tsvector('kaasearch', 'ǵalaba ózbek') AS normalize_match; normalize_match ----------------- t (1 row) SELECT ts_lexize('kaasearch_dict', 'o''zbekstanda'); ts_lexize ------------- {ózbekstan} (1 row) SELECT ts_headline('kaasearch', 'Bizler O''zbekstan mektebinde oqıdıq.', to_tsquery('kaasearch', 'ózbekstan & mektep')); ts_headline --------------------------------------------------- Bizler Ózbekstan mektebinde oqıdıq. (1 row) SELECT alias, token, lexemes FROM ts_debug('kaasearch', 'o''zbek tili'); alias | token | lexemes -----------+-------+--------- word | ózbek | {ózbek} blank | | asciiword | tili | {til} (3 rows) -- 19b. Letters that old fonts use in place of Karakalpak letters: ѳ → ө, ӊ → ң, ӯ ӳ ӱ → ў, ӽ → ҳ SELECT to_tsvector('kaasearch', 'Нѳкис оқытыўшыныӊ қорғаӯ ӼАЯЛЛАР'); to_tsvector --------------------------------------------- 'hayal':4 'nókis':1 'oqıtıwshı':2 'qorǵa':3 (1 row) SELECT to_tsvector('kaasearch', 'Нѳкис оқытыўшыныӊ') = to_tsvector('kaasearch', 'Нөкис оқытыўшының') AS old_font_match; old_font_match ---------------- t (1 row) -- 19c. Punctuation around a word is left out: in a database with the C locale the default parser -- gives «Mektep» or “Мектеп” as one token SELECT ts_lexize('kaasearch_dict', '«Mektep»'), ts_lexize('kaasearch_dict', '“Мектепке”'), ts_lexize('kaasearch_dict', '—'), ts_lexize('kaasearch_dict', '…'); ts_lexize | ts_lexize | ts_lexize | ts_lexize -----------+-----------+-----------+----------- {mektep} | {mektep} | {} | {} (1 row) -- 20. Verb forms: tense, person, negation and voice suffixes are stripped SELECT to_tsvector('kaasearch', 'jazıladı barmadım keleseń oqıp otır'); to_tsvector ------------------------------------------ 'bar':2 'jaz':1 'kel':3 'oqı':4 'otır':5 (1 row) -- 21. A stem that is a stop word is dropped, whatever its suffixes (eken, ekenin); other words are -- kept in every form (bardı, bardıq, barǵan → bar) SELECT to_tsvector('kaasearch', 'eken ekenin mektep'); to_tsvector ------------- 'mektep':3 (1 row) SELECT to_tsvector('kaasearch', 'bardı bardıq barǵan bolǵan bolıp'); to_tsvector ----------------------- 'bar':1,2,3 'bol':4,5 (1 row) -- 22. A stem of two words gives one lexeme per word SELECT to_tsvector('kaasearch', 'ákeldi'); to_tsvector ---------------- 'al':1 'kel':1 (1 row) SELECT to_tsvector('kaasearch', 'Ол китапты әкелди') @@ to_tsquery('kaasearch', 'alıp & keldi') AS two_word_stem; two_word_stem --------------- t (1 row) -- 23. Search: every form of a word finds the others CREATE TEMP TABLE docs(id int, body text); INSERT INTO docs VALUES (1, 'Bizler mektebimizde jańa kitaplardı oqıdıq.'), (2, 'Мектепке барған балалар китап оқыйды.'), (3, 'Qaraqalpaq xalqınıń tariyxı haqqında maqala.'), (4, 'Ol keshe qalaǵa ketti.'); SELECT id FROM docs WHERE to_tsvector('kaasearch', body) @@ plainto_tsquery('kaasearch', 'mektep kitap') ORDER BY id; id ---- 1 2 (2 rows) SELECT id FROM docs WHERE to_tsvector('kaasearch', body) @@ plainto_tsquery('kaasearch', 'xalıqtıń tariyxın') ORDER BY id; id ---- 3 (1 row) SELECT id FROM docs WHERE to_tsvector('kaasearch', body) @@ websearch_to_tsquery('kaasearch', 'qala or oqıydı') ORDER BY id; id ---- 1 2 4 (3 rows) -- 24. Ranking and an index CREATE INDEX docs_fts ON docs USING gin (to_tsvector('kaasearch', body)); SELECT id, round(ts_rank(to_tsvector('kaasearch', body), q)::numeric, 4) AS rank FROM docs, to_tsquery('kaasearch', 'kitap | mektep') q WHERE to_tsvector('kaasearch', body) @@ q ORDER BY rank DESC, id; id | rank ----+-------- 1 | 0.0608 2 | 0.0608 (2 rows) -- 25. Phrase search: words in a row, stop words keep their position SELECT to_tsvector('kaasearch', 'Qaraqalpaq xalqınıń tariyxı') @@ phraseto_tsquery('kaasearch', 'xalıq tariyxı') AS phrase_match; phrase_match -------------- t (1 row) SELECT to_tsvector('kaasearch', 'tariyxı xalqınıń') @@ phraseto_tsquery('kaasearch', 'xalıq tariyxı') AS phrase_wrong_order; phrase_wrong_order -------------------- f (1 row) SELECT phraseto_tsquery('kaasearch', 'mektep hám kitap'); phraseto_tsquery ---------------------- 'mektep' <2> 'kitap' (1 row) -- 26. ts_headline marks the forms found SELECT ts_headline('kaasearch', 'Bizler mektebimizde jańa kitaplardı oqıdıq.', to_tsquery('kaasearch', 'mektep & kitap')); ts_headline ----------------------------------------------------------- Bizler mektebimizde jańa kitaplardı oqıdıq. (1 row) -- 27. ts_debug shows the dictionary used for each token SELECT alias, token, dictionaries, lexemes FROM ts_debug('kaasearch', 'Mektepke 2 bala keldi'); alias | token | dictionaries | lexemes -----------+----------+------------------+---------- asciiword | Mektepke | {kaasearch_dict} | {mektep} blank | | {} | uint | 2 | {simple} | {2} blank | | {} | asciiword | bala | {kaasearch_dict} | {bala} blank | | {} | asciiword | keldi | {kaasearch_dict} | {kel} (7 rows) -- 28. SQL functions SELECT kaa_stem('mektebimizde'), kaa_stem('Мектепке'), kaa_stem('ákeldi'), kaa_stem(''); kaa_stem | kaa_stem | kaa_stem | kaa_stem ----------+----------+----------+---------- mektep | mektep | alıp kel | (1 row) SELECT kaa_normalize('Мектепке BARDIQ, g''alaba! ÍDÍS'); kaa_normalize ------------------------------- mektepke bardiq, ǵalaba! ıdıs (1 row) SELECT * FROM kaa_analyze('kelgenligin'); word | stem | segments | classes | source -------------+------+-------------------+---------------------+--------- kelgenligin | kel | {kel,gen,lig,i,n} | {PTCP,NMLZ,PX,ACCP} | lexicon (1 row) SELECT * FROM kaa_analyze('maǵan'); word | stem | segments | classes | source -------+------+----------+---------+-------- maǵan | men | {maǵan} | {} | list (1 row) SELECT * FROM kaa_analyze('Lissabonnan'); word | stem | segments | classes | source -------------+----------+----------------+---------+-------- lissabonnan | lissabon | {lissabon,nan} | {ABL} | safe (1 row) SELECT kaa_stem(NULL) IS NULL AS strict; strict -------- t (1 row) -- 29. User lexicon: stems the built-in lexicon does not know SELECT to_tsvector('kaasearch', 'Lissabonǵa skanerledi'); to_tsvector ------------------------------- 'lissabonǵa':1 'skanerledi':2 (1 row) ALTER TEXT SEARCH DICTIONARY kaasearch_dict (StopWords = kaasearch, Lexicon = kaasearch_example); SELECT to_tsvector('kaasearch', 'Lissabonǵa skanerledi'); to_tsvector --------------------------- 'lissabon':1 'skanerle':2 (1 row) ALTER TEXT SEARCH DICTIONARY kaasearch_dict (Lexicon); SELECT to_tsvector('kaasearch', 'Lissabonǵa skanerledi'); to_tsvector ------------------------------- 'lissabonǵa':1 'skanerledi':2 (1 row) -- 30. Dictionary option errors -- (the message has the install path, so only the error code is printed) DO $$ BEGIN ALTER TEXT SEARCH DICTIONARY kaasearch_dict (Lexicon = no_such_lexicon); EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'missing lexicon: %', SQLSTATE; END $$; NOTICE: missing lexicon: F0000 CREATE TEXT SEARCH DICTIONARY kaa_bad (TEMPLATE = kaasearch_template, Foo = bar); ERROR: unrecognized parameter: "foo" CREATE TEXT SEARCH DICTIONARY kaa_nostop (TEMPLATE = kaasearch_template); SELECT ts_lexize('kaa_nostop', 'maǵan'), ts_lexize('kaasearch_dict', 'maǵan'); ts_lexize | ts_lexize -----------+----------- {men} | {men} (1 row) DROP TEXT SEARCH DICTIONARY kaa_nostop; -- Clean up DROP TABLE docs; DROP EXTENSION pg_kaasearch;