SQL functions

Use SQL functions in matching models to normalize extracted values and compare candidate records. MatchingModel uses them in variable.expression and feature cases; BulkMatchingModel uses them in column.source and feature cases.

MDMbox installs four matching helpers in the public schema and enables the PostgreSQL extensions unaccent, fuzzystrmatch, and pg_trgm during startup. The examples below run against the PostgreSQL database shared by MDMbox and Aidbox. Extension functions can vary with your PostgreSQL version; the comparison functions listed below are available on PostgreSQL 14 and later unless stated otherwise.

The canonical mdm_ names below are introduced in the next release and are available in development builds. On earlier releases, use the corresponding legacy names.

Normalization helpers

All three helpers accept text and return text. They preserve SQL NULL and return '' for an empty string.

Accent removal uses the installed PostgreSQL unaccent dictionary, including its rules for ligatures such as Æ → AE.

The extension also provides unaccent(text) and unaccent(regdictionary, text); the latter selects a dictionary explicitly. The MDMbox helpers provide immutable normalization expressions for indexing.

SELECT
  public.mdm_unaccent(' José da Silva ') AS unaccented,
  public.mdm_unaccent_upper(' José da Silva ') AS uppercase,
  public.mdm_unaccent_upper_no_spaces(' José da Silva ') AS compact;

For names where only surrounding spaces should be ignored, use btrim:

SELECT btrim(public.mdm_unaccent_upper(' José da Silva ')) AS trimmed;
-- JOSE DA SILVA

The compact helper removes ordinary spaces throughout the value. Tabs and line breaks remain:

SELECT public.mdm_unaccent_upper_no_spaces(E'José\tda Silva\n')
       = E'JOSE\tDASILVA\n' AS preserves_tabs_and_newlines;
-- true

Use normalization consistently on both sides of a comparison. For example, extract a family name in a MatchingModel:

{
  "name": "family",
  "expression": "btrim(public.mdm_unaccent_upper(#.resource->'name'->0->>'family'))"
}

For a BulkMatchingModel column, use the same function with its source expression:

{
  "name": "family",
  "type": "text",
  "source": "btrim(public.mdm_unaccent_upper(resource->'name'->0->>'family'))"
}

Jaro–Winkler similarity

public.mdm_jaro_winkler(text, text) returns double precision between 0 and 1; higher values mean greater similarity. It compares the supplied characters directly, so normalize case, accents, and spaces before calling it when those differences should be ignored.

This function adds a common-prefix bonus when the Jaro score exceeds 0.7. The bonus uses the entire matching prefix, with a scaling factor of the smaller of 0.1 and 1 / longest_input_length. The prefix is not capped at four characters, so results can differ from other Jaro–Winkler libraries.

SELECT
  public.mdm_jaro_winkler('MARTHA', 'MARHTA') AS transposed,
  public.mdm_jaro_winkler(
    btrim(public.mdm_unaccent_upper(' José ')),
    btrim(public.mdm_unaccent_upper('jose'))
  ) AS normalized;
-- transposed: approximately 0.961111; normalized: 1

Similarity values feed feature cases; the selected cases assign the model's match weights. For example, a feature comparing normalized family-name variables could use:

{
  "name": "family",
  "case": [
    { "expression": "l.#family IS NULL OR r.#family IS NULL", "weight": -2 },
    { "expression": "l.#family = r.#family", "weight": 10 },
    { "expression": "public.mdm_jaro_winkler(l.#family, r.#family) >= 0.9", "weight": 3 },
    { "else": -8 }
  ]
}

The weights and cutoff above are illustrative. Tune them against your own records.

PostgreSQL comparison functions

Edit distance and phonetic codes

These functions come from fuzzystrmatch:

Levenshtein also accepts insertion, deletion, and substitution costs in that order; each defaults to 1. Its cost overload is levenshtein(text, text, integer, integer, integer). The corresponding cutoff overload is levenshtein_less_equal(text, text, integer, integer, integer, integer), with the cutoff last. Levenshtein and Metaphone inputs are limited to 255 characters. Soundex and Metaphone variants have limitations with multibyte text; choose comparisons suited to your names and languages.

SELECT
  levenshtein('SMITH', 'SMYTH') AS edits,
  levenshtein_less_equal('SMITH', 'SMYTH', 2) AS bounded_edits,
  soundex('Robert') = soundex('Rupert') AS same_soundex,
  difference('Robert', 'Rupert') AS soundex_positions,
  metaphone('Smith', 10) AS metaphone_code,
  dmetaphone('Smith') AS primary_code,
  dmetaphone_alt('Smith') AS alternate_code;
-- 1, 1, true, 4, SM0, SM0, XMT

PostgreSQL 16 and later also provide daitch_mokotoff(text), returning text[] of possible phonetic codes. Compare arrays with && to test for a shared code.

Trigram similarity

These functions come from pg_trgm:

Higher scores mean greater similarity. pg_trgm normally ignores case and non-alphanumeric separators; it still distinguishes accented letters. word_similarity and strict_word_similarity depend on argument order. Use an explicit numeric cutoff in feature expressions, for example similarity(l.#family, r.#family) >= 0.8.

The extension also exposes the deprecated show_limit() → real and set_limit(real) → real for the % operator's session threshold. Use explicit comparison cutoffs in matching models; see the PostgreSQL reference for operators and their settings.

SELECT
  similarity('SMITH', 'smith') AS whole_string,
  word_similarity('SMITH', 'JOHN SMITH') AS within_string,
  strict_word_similarity('SMITH', 'JOHN SMITH') AS whole_word;
-- 1, 1, 1

Missing values and indexes

The matching helpers and comparison functions above return SQL NULL when a required input is NULL. An empty string is a present value: normalize it to NULL with NULLIF(expression, '') if your model should treat it as missing. Handle missing values in a feature case before comparing strings; a SQL NULL comparison does not satisfy a case.

All four MDMbox helpers are declared IMMUTABLE, so they can be used in expression indexes. Of these helpers, mdm_unaccent_upper and mdm_jaro_winkler are also PARALLEL SAFE. Custom SQL functions should be declared PARALLEL SAFE only when their operations support parallel queries.

Legacy names

Use canonical names in new models. Both sets of names belong to the public schema; PostgreSQL extension functions keep their existing names.

When upgrading to the version with canonical names, MDMbox keeps each legacy name that was already installed as a compatibility alias. Existing models, expression indexes, views, and Continuous matching processes keep working; you do not need to rewrite their expressions or rebuild their indexes for this rename. A clean installation of that version creates only canonical names, so a model imported from an older installation must use the canonical names.

Legacy aliases return the same results as their canonical functions. Function ownership and execution permissions are retained during the upgrade.

See Matching models for expression syntax and Mathematical details for how feature weights become match scores.