CREATE EXTENSION pg_sift; CREATE FUNCTION toks(doc text, stemmer text DEFAULT NULL, c bool DEFAULT NULL, a bool DEFAULT NULL) RETURNS text LANGUAGE sql AS $$ SELECT string_agg(token, ' ' ORDER BY pos) FROM sift.tokenize(doc, NULL, stemmer, c, a) $$; -- positions and character offsets SELECT *, substr(d, start_offset + 1, end_offset - start_offset) AS source FROM (VALUES ('The Quick-brown fox jumps over the lazy dog.')) v(d), sift.tokenize(d); d | token | pos | start_offset | end_offset | source -----------------------------------------------+-------+-----+--------------+------------+-------- The Quick-brown fox jumps over the lazy dog. | the | 0 | 0 | 3 | The The Quick-brown fox jumps over the lazy dog. | quick | 1 | 5 | 10 | Quick The Quick-brown fox jumps over the lazy dog. | brown | 2 | 11 | 16 | brown The Quick-brown fox jumps over the lazy dog. | fox | 3 | 17 | 20 | fox The Quick-brown fox jumps over the lazy dog. | jumps | 4 | 21 | 26 | jumps The Quick-brown fox jumps over the lazy dog. | over | 5 | 27 | 31 | over The Quick-brown fox jumps over the lazy dog. | the | 6 | 32 | 35 | the The Quick-brown fox jumps over the lazy dog. | lazy | 7 | 36 | 40 | lazy The Quick-brown fox jumps over the lazy dog. | dog | 8 | 41 | 44 | dog (9 rows) SELECT *, substr(d, start_offset + 1, end_offset - start_offset) AS source FROM (VALUES ('Crème brûlée à São Paulo')) v(d), sift.tokenize(d); d | token | pos | start_offset | end_offset | source --------------------------+--------+-----+--------------+------------+-------- Crème brûlée à São Paulo | creme | 0 | 0 | 5 | Crème Crème brûlée à São Paulo | brulee | 1 | 6 | 12 | brûlée Crème brûlée à São Paulo | a | 2 | 13 | 14 | à Crème brûlée à São Paulo | sao | 3 | 15 | 18 | São Crème brûlée à São Paulo | paulo | 4 | 19 | 24 | Paulo (5 rows) -- empty, NULL, separators only SELECT count(*) FROM sift.tokenize(''); count ------- 0 (1 row) SELECT count(*) FROM sift.tokenize(NULL); count ------- 0 (1 row) SELECT count(*) FROM sift.tokenize(' .,;:!?-_/\()[]{}<>"''`~@#$%^&*+=| '); count ------- 0 (1 row) -- apostrophes, hyphens, underscores, periods and numbers separate SELECT d, toks(d) FROM (VALUES ('don''t'), ('O''Neil''s'), ('John’s book'), ('rock''n''roll'), ('l''amour'), ('ʼokina ʻokina'), ('e-mail snake_case U.S.A.'), ('3.14 1,000 2024-10-11 10:30'), ('mp3 covid19 x86_64 C++ C# .NET'), ('user@example.com https://a.b/c?d=e')) v(d); d | toks ------------------------------------+---------------------------------- don't | don t O'Neil's | o neil s John’s book | john s book rock'n'roll | rock n roll l'amour | l amour ʼokina ʻokina | okina ʻokina e-mail snake_case U.S.A. | e mail snake case u s a 3.14 1,000 2024-10-11 10:30 | 3 14 1 000 2024 10 11 10 30 mp3 covid19 x86_64 C++ C# .NET | mp3 covid19 x86 64 c c net user@example.com https://a.b/c?d=e | user example com https a b c d e (10 rows) -- case folding (full Unicode case folding) SELECT d, toks(d) FROM (VALUES ('HELLO World'), ('STRASSE Straße ẞ'), ('ΣΊΣΥΦΟΣ σίσυφος'), ('Džemal DŽ'), ('İstanbul ISTANBUL ıi'), ('financial ff'), ('ＦＵＬＬ　ｗｉｄｔｈ ０１２'), ('ΜΆΪΟΣ'), ('ĞÜŞÇÖ'), ('ԱՐԱՄ'), ('ᏣᎳᎩ')) v(d); d | toks -----------------------------+---------------------- HELLO World | hello world STRASSE Straße ẞ | strasse strasse ss ΣΊΣΥΦΟΣ σίσυφος | σισυφοσ σισυφοσ Džemal DŽ | dzemal dz İstanbul ISTANBUL ıi | istanbul istanbul ii financial ff | financial ff ＦＵＬＬ　ｗｉｄｔｈ ０１２ | full width 012 ΜΆΪΟΣ | μαιοσ ĞÜŞÇÖ | gusco ԱՐԱՄ | արամ ᏣᎳᎩ | ᏣᎳᎩ (11 rows) -- accent and compatibility folding SELECT d, toks(d) FROM (VALUES ('Crème Brûlée NAÏVE Ångström'), ('Łódź Øresund Æsir Œuvre þorn Đorđe'), ('x² H₂O ① ½'), ('Ελληνικά ἀρχή ᾳ'), ('Москва Ёлка Йошкар-Ола їжак ў'), ('mañana Nguyễn'), ('مُحَمَّد'), ('עִבְרִית'), ('ﷲ'), ('ｶﾀｶﾅ'), ('㎏ ℌ ℃')) v(d); d | toks ------------------------------------+--------------------------------------- Crème Brûlée NAÏVE Ångström | creme brulee naive angstrom Łódź Øresund Æsir Œuvre þorn Đorđe | lodz oresund aesir oeuvre thorn dorde x² H₂O ① ½ | x2 h2o 1 ½ Ελληνικά ἀρχή ᾳ | ελληνικα αρχη α Москва Ёлка Йошкар-Ола їжак ў | москва елка йошкар ола іжак ў mañana Nguyễn | manana nguyen مُحَمَّد | محمد עִבְרִית | עברית ﷲ | الله ｶﾀｶﾅ | カタカナ ㎏ ℌ ℃ | h (11 rows) -- decomposed input folds per code point SELECT toks(E'Crème'), toks(E'́abc'), toks(E'́'); toks | toks | toks -------+------+------ creme | abc | (1 row) -- folding modes: preserve, case only, accent only SELECT d, toks(d, NULL, false, false) AS preserve, toks(d, NULL, true, false) AS case_only, toks(d, NULL, false, true) AS accent_only FROM (VALUES ('Crème BRÛLÉE'), ('Straße fi'), ('ΣΊΣΥΦΟΣ'), ('ＡＢＣ')) v(d); d | preserve | case_only | accent_only --------------+--------------+--------------+-------------- Crème BRÛLÉE | Crème BRÛLÉE | crème brûlée | Creme BRULEE Straße fi | Straße fi | strasse fi | Straße fi ΣΊΣΥΦΟΣ | ΣΊΣΥΦΟΣ | σίσυφοσ | ΣΙΣΥΦΟΣ ＡＢＣ | ＡＢＣ | ａｂｃ | ABC (4 rows) -- scripts: Han and Hiragana per character, other scripts as runs SELECT d, toks(d) FROM (VALUES ('東京タワーに行きました'), ('ひらがな カタカナ'), ('中文分词test測試'), ('한국어 텍스트'), ('ภาษาไทย'), ('हिन्दी भाषा'), ('বাংলা'), ('ქართული'), ('𠀀𠀁 々')) v(d); d | toks ------------------------+-------------------------------- 東京タワーに行きました | 東 京 タワー に 行 き ま し た ひらがな カタカナ | ひ ら が な カタカナ 中文分词test測試 | 中 文 分 词 test 測 試 한국어 텍스트 | 한국어 텍스트 ภาษาไทย | ภาษาไทย हिन्दी भाषा | हिन्दी भाषा বাংলা | বাংলা ქართული | ქართული 𠀀𠀁 々 | 𠀀 𠀁 々 (9 rows) -- emoji: one token per pictograph, modifiers dropped, flags pair up SELECT d, toks(d) FROM (VALUES ('👍🏽👍'), ('I ❤️ NY'), ('👨‍👩‍👧'), ('🇧🇷🇺🇸🇫'), ('1️⃣ #️⃣'), ('™ © ®'), ('👍word👍'), ('🏴󠁧󠁢󠁳󠁣󠁴󠁿')) v(d); d | toks ----------+------------ 👍🏽👍 | 👍 👍 I ❤️ NY | i ❤ ny 👨‍👩‍👧 | 👨 👩 👧 🇧🇷🇺🇸🇫 | 🇧🇷 🇺🇸 🇫 1️⃣ #️⃣ | 1 ™ © ® | 👍word👍 | 👍 word 👍 🏴󠁧󠁢󠁳󠁣󠁴󠁿 | 🏴 (8 rows) -- default ignorables join, zero width space separates SELECT toks(E'co­op'), toks(E'zero​width'), toks(E'zw‍j'), toks(E'bom'), toks(E'a‏b'); toks | toks | toks | toks | toks ------+------------+------+------+------ coop | zero width | zwj | bom | ab (1 row) -- a token over SIFT_MAX_TERM_BYTES is its start (cut at a character boundary), U+FFFF and a hash of all of it SELECT pos, length(token), octet_length(token), end_offset FROM sift.tokenize(repeat('a', 300) || ' ' || repeat('é', 200) || ' ' || repeat('東', 10) || ' x'); pos | length | octet_length | end_offset -----+--------+--------------+------------ 0 | 253 | 255 | 300 1 | 200 | 200 | 501 2 | 1 | 3 | 503 3 | 1 | 3 | 504 4 | 1 | 3 | 505 5 | 1 | 3 | 506 6 | 1 | 3 | 507 7 | 1 | 3 | 508 8 | 1 | 3 | 509 9 | 1 | 3 | 510 10 | 1 | 3 | 511 11 | 1 | 3 | 512 12 | 1 | 1 | 514 (13 rows) SELECT octet_length(token), length(token) FROM sift.tokenize(repeat('é', 200), case_folding => false, accent_folding => false); octet_length | length --------------+-------- 255 | 135 (1 row) SELECT octet_length(token) FROM sift.tokenize('a' || repeat('ffi', 100)); octet_length -------------- 255 (1 row) -- properties over random strings of mixed scripts (deterministic seed) SELECT setseed(0.42); setseed --------- (1 row) CREATE TEMP TABLE rnd AS SELECT i, (SELECT string_agg(chr(CASE WHEN r < 0.3 THEN 97 + (random() * 25)::int WHEN r < 0.4 THEN 32 WHEN r < 0.5 THEN 192 + (random() * 300)::int WHEN r < 0.6 THEN 768 + (random() * 111)::int WHEN r < 0.7 THEN 19968 + (random() * 200)::int WHEN r < 0.8 THEN 127744 + (random() * 300)::int WHEN r < 0.9 THEN 33 + (random() * 30)::int ELSE 1 + (random() * 55000)::int END), '') FROM (SELECT random() AS r, i FROM generate_series(1, 1 + (random() * 80)::int)) s) AS d FROM generate_series(1, 2000) i; -- positions are dense, offsets increase and stay inside the document SELECT count(*) AS bad FROM rnd, LATERAL ( SELECT max(pos) = count(*) - 1 AND count(DISTINCT pos) = count(*) AS dense, bool_and(start_offset < end_offset AND end_offset <= length(d)) AS inside, bool_and(octet_length(token) BETWEEN 1 AND 255) AS sized FROM sift.tokenize(d)) t WHERE NOT (coalesce(dense, true) AND coalesce(inside, true) AND coalesce(sized, true)); bad ----- 0 (1 row) -- tokenizing is concatenation independent across a separator SELECT count(*) AS bad FROM rnd a JOIN rnd b ON b.i = a.i % 2000 + 1 WHERE toks(a.d || ' ' || b.d) IS DISTINCT FROM nullif(concat_ws(' ', toks(a.d), toks(b.d)), ''); bad ----- 0 (1 row) -- tokens are already folded: re-analyzing a token yields itself SELECT count(*) AS bad FROM rnd, sift.tokenize(d) t WHERE toks(t.token) IS DISTINCT FROM t.token; bad ----- 0 (1 row) -- only NULL selects the default analyzer; a non-NULL zero OID is opened SELECT token FROM sift.tokenize('Hello', NULL); token ------- hello (1 row) SELECT token FROM sift.tokenize('Hello', 0::oid::regclass); ERROR: could not open relation with OID 0 -- the analyzer of a relation that is not a sift index CREATE TABLE plain (body text); CREATE INDEX plain_btree ON plain (body); SELECT * FROM sift.tokenize('x', 'plain_btree'); ERROR: "plain_btree" is not a sift index SELECT * FROM sift.tokenize('x', 'plain'); ERROR: "plain" is not an index DROP TABLE plain; DROP FUNCTION toks; DROP EXTENSION pg_sift;