CREATE INDEX

Create an inverted index on a table for full-text search.

Syntax

Column specification

Each column can optionally specify a text search dictionary for linguistic processing:

Parameters

Parameter	Description
index_name	Name of the index to create
table_name	Table to index
column_name	Column to include in the index
dictionary_name	Optional text search dictionary for text columns. Without a dictionary, exact token matching is used

Examples

Inverted index for full-text search

CREATE INDEX idx_articles_search ON articles
    USING inverted (id, title english_dict, body english_dict);

Query the inverted index

SereneDB Extension

Querying an index directly by name (using the index name in FROM) is a SereneDB extension to standard SQL. Search indexes carry their own schema and semantics — for example, text columns are tokenized and may behave differently from the underlying table columns. Querying the index directly makes this distinction explicit.

SELECT id, title
FROM idx_articles_search
WHERE PHRASE(body, 'vector search')
ORDER BY id;

Inverted index with a custom dictionary

CREATE TEXT SEARCH DICTIONARY french_dict (
    template = 'text',
    locale = 'fr',
    case = 'lower',
    stemming = true,
    accent = true,
    frequency = true,
    position = true
);

CREATE INDEX idx_articles_fr ON articles
    USING inverted (id, body french_dict);

Fuzzy search

Inverted indexes support fuzzy matching via LEVENSHTEIN_MATCH (edit distance) and NGRAM_MATCH (n-gram similarity). See the Fuzzy Search cookbook for detailed examples and guidance on choosing between them.

See also

• DROP INDEX
• CREATE TEXT SEARCH DICTIONARY
• VACUUM