DOCSEARCHbyAlgolia
Submit Docs

pg_similarity

repository·master·Indexed 19 days ago

https://github.com/eulerto/pg_similarity

A PostgreSQL extension providing similarity algorithms such as Levenshtein, Jaro-Winkler, and Cosine distance for fuzzy string matching. It includes specialized functions for numeric scoring, operators for boolean matching based on thresholds, and session variables to configure tokenizers and normalization.

Tokens
2.2K
Snippets
4
Records
6
Agent score
14%

What's inside pg_similarity

  1. How pg_similarity components work together

    master

    The pg_similarity extension consists of three interconnected components:

    • Functions: Implement specific similarity algorithms (e.g., Levenshtein, Cosine) and can be used as User Defined Functions (UDFs).
    • Operators: Built on top of similarity functions. They compare the result of a function against a user-defined threshold to return a boolean match.
    • Session Variables: Store parameters for the similarity functions (like tokenizers or thresholds) which can be modified at runtime using SET commands.

  2. Configure similarity parameters via session variables

    master

    Similarity behavior is controlled by three classes of session variables:

    1. tokenizer: Controls string tokenization. Valid values are:
      • alnum: Delimiters are non-alphanumeric characters.
      • gram: Uses n-grams (sliding window).
      • word: Delimiters are whitespace characters.
      • camelcase: Delimiters are capitalized characters.
    2. threshold: Controls matching flexibility. Values range from 0.0 to 1.0. The default is 0.7. If the function result is $\ge$ threshold, it is a match.
    3. normalized: A boolean controlling whether the coefficient/distance is normalized between 0.0 and 1.0. Default is true.

    You can view and set these at runtime using SHOW and SET commands.

    -- View current threshold
SHOW pg_similarity.levenshtein_threshold;

-- Change threshold
SET pg_similarity.levenshtein_threshold TO 0.5;

-- Change tokenizer
SET pg_similarity.cosine_tokenizer TO camelcase;

-- Disable normalization
SET pg_similarity.euclidean_is_normalized TO false;

  3. Install pg_similarity on UNIX-based Operating Systems

    master

    To install pg_similarity on UNIX-based systems, you must build the extension from source and load it into your PostgreSQL database.

    1. Extract the source code.
    2. Edit the Makefile if the PG_CONFIG path needs adjustment.
    3. Run make and make install.
    4. Connect to your database and run CREATE EXTENSION pg_similarity;.

    To ensure configuration persistence, copy the sample configuration file pg_similarity.conf.sample from the tarball to your PGDATA directory as pg_similarity.conf, and add include 'pg_similarity.conf' to your postgresql.conf file.

    $ tar -zxf pg_similarity-1.0.tgz
$ cd pg_similarity-1.0
$ $EDITOR Makefile # edit PG_CONFIG iif necessary
$ make
$ make install
$ psql mydb

mydb=# CREATE EXTENSION pg_similarity;

  4. Install pg_similarity on Windows (MSVC)

    master

    If you are using a PostgreSQL build on Windows via MSVC, follow these steps:

    1. Edit pg_similarity.vcxproj and replace c:\postgres\pg130 with your actual PostgreSQL prefix directory.
    2. Open the project in MS Visual Studio and build it.
    3. Copy pg_similarity.dll to the directory returned by pg_config --pkglibdir.
    4. Copy pg_similarity.control and all pg_similarity--*.sql files to the directory returned by pg_config --sharedir/SHAREDIR/extension.

  5. Use similarity functions and operators in queries

    master

    You can use similarity functions to get a numeric score or operators to perform boolean similarity matching.

    Functions return a float8 score:

    SELECT a, b, cosine(a, b) FROM table_a, table_b;

    Operators use the current session threshold to return true/false:

    -- Levenshtein operator (~==)
SELECT a, b, lev(a, b) FROM foo, bar WHERE a ~== b;

-- Q-Gram operator (~~~)
SELECT a, b, qgram(a, b) FROM foo, bar WHERE a ~~~ b;
    -- Example: Using Levenshtein operator with a custom threshold
SET pg_similarity.levenshtein_threshold TO 0.5;
SELECT a, b, lev(a, b) FROM foo, bar WHERE a ~== b;

  6. Reference of similarity functions and operators

    master

    The extension provides various algorithms. Each algorithm has a corresponding function, an operator, and specific session variable parameters. Some algorithms support indexing.

    |---|---|---|---|---|
|L1 Distance|block(text, text)|~++|yes|pg_similarity.block_tokenizer, pg_similarity.block_threshold, pg_similarity.block_is_normalized|
|Cosine Distance|cosine(text, text)|~##|yes|pg_similarity.cosine_tokenizer, pg_similarity.cosine_threshold, pg_similarity.cosine_is_normalized|
|Dice Coefficient|dice(text, text)|~-~|yes|pg_similarity.dice_tokenizer, pg_similarity.dice_threshold, pg_similarity.dice_is_normalized|
|Euclidean Distance|euclidean(text, text)|~!!|yes|pg_similarity.euclidean_tokenizer, pg_similarity.euclidean_threshold, pg_similarity.euclidean_is_normalized|
|Hamming Distance|hamming(bit varying, bit varying)|~@~|no|pg_similarity.hamming_threshold, pg_similarity.hamming_is_normalized|
|Jaccard Coefficient|jaccard(text, text)|~??|yes|pg_similarity.jaccard_tokenizer, pg_similarity.jaccard_threshold, pg_similarity.jaccard_is_normalized|
|Jaro Distance|jaro(text, text)|~%%|no|pg_similarity.jaro_threshold, pg_similarity.jaro_is_normalized|
|Jaro-Winkler Distance|jarowinkler(text, text)|~@@|no|pg_similarity.jarowinkler_threshold, pg_similarity.jarowinkler_is_normalized|
|Levenshtein Distance|lev(text, text)|~==|no|pg_similarity.levenshtein_threshold, pg_similarity.levenshtein_is_normalized|
|Matching Coefficient|matchingcoefficient(text, text)|~^^|yes|pg_similarity.matching_tokenizer, pg_similarity.matching_threshold, pg_similarity.matching_is_normalized|
|Monge-Elkan Coefficient|mongeelkan(text, text)|~|||no|pg_similarity.mongeelkan_tokenizer, pg_similarity.mongeelkan_threshold, pg_similarity.mongeelkan_is_normalized|
|Needleman-Wunsch Coefficient|needlemanwunsch(text, text)|~#~|no|pg_similarity.nw_threshold, pg_similarity.nw_is_normalized|
|Overlap Coefficient|overlapcoefficient(text, text)|~**|yes|pg_similarity.overlap_tokenizer, pg_similarity.overlap_threshold, pg_similarity.overlap_is_normalized|
|Q-Gram Distance|qgram(text, text)|~~~|yes|pg_similarity.qgram_threshold, pg_similarity.qgram_is_normalized|
|Smith-Waterman Coefficient|smithwaterman(text, text)|~=~|no|pg_similarity.sw_threshold, pg_similarity.sw_is_normalized|
|Smith-Waterman-Gotoh Coefficient|smithwatermangotoh(text, text)|~!~|no|pg_similarity.swg_threshold, pg_similarity.swg_is_normalized|
|Soundex Distance|soundex(text, text)|~*~|no|