-- 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;