Trigram similarity (pg_trgm) with German umlauts

For starters: A German poet is still a Poet. 'Poet' <> 'Pöt'; 'oe' <> 'ö'. The convention to replace 'ö' with 'oe' is largely out of use nowadays. See:

• https://de.wikipedia.org/wiki/Umlaut#Darstellung_von_Umlauten

I have had related problems many times. The additional module unaccent is instrumental to cover spelling variants and typos on both sides. Install it once per database:

CREATE EXTENSION unaccent;

See:

• Does PostgreSQL support “accent insensitive” collations?

The tricky part is to get index support with it. Details may be hard for beginners.

Trigram similarity

Then create the wrapper function as instructed here (same link again):

• Does PostgreSQL support “accent insensitive” collations?

Create a new index with it:

CREATE INDEX names_trgm_idx ON names USING gin (f_unaccent(name) gin_trgm_ops);

Query demo:

SELECT name
     , similarity(f_unaccent(name), f_unaccent('Schoenstraße')) AS sim_unaccent
     , similarity(name, 'Schoenstraße') AS sim
FROM   names
WHERE  f_unaccent(name) % f_unaccent('Schoenstraße')
ORDER  BY f_unaccent(name) <-> f_unaccent('Schoenstraße')
        , name <-> 'Schoenstraße';  -- best match first

Sorting by name <-> 'Schoenstraße' additionally serves as tiebreaker to get best matches first (after filtering cheaply with the index).

Consider a GiST index instead of GIN for "nearest neighbor"-type queries with LIMIT. See:

• Best index for similarity function

Full text search (FTS or just TS)

Consider full text search to just match complete words (after stemming), or with prefix matching / phrase search ... See:

• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

The unaccent module provides a dictionary for integration in a TEXT SEARCH CONFIGURATION:

CREATE TEXT SEARCH CONFIGURATION de (COPY = german);
ALTER TEXT SEARCH CONFIGURATION de
ALTER MAPPING FOR hword, hword_part, word WITH unaccent, german_stem;

Index based on it:

CREATE INDEX names_fts_idx ON names USING GIN (to_tsvector('de', name));

Alternatively, since you are dealing with names, you might base it on the simple text search configuration instead of german. No stop words, no stemming. Names don't necessarily follow language-specific rules.

Query demo:

SELECT name
     , ts_lexize('unaccent', name)
     , to_tsvector('de', name)
     , to_tsquery('de', 'Schoenstraße')
     , to_tsvector('de', name) @@ to_tsquery('de', 'Schoenstraße') AS match
     , similarity(name, 'Schoenstraße')
FROM   names
WHERE  to_tsvector('de', name) @@ to_tsquery('de', 'Schoenstraße')
ORDER  BY name <-> 'Schoenstraße';  -- exact matches first

Again, sorting by name <-> 'Schoenstraße' serves as tiebreaker, maybe additionally to other sort criteria.

Note, however, that looking for 'Schoenstraße' with FTS does not find 'Schönstraße', as unaccent() does not change 'oe', and maps 'ö' to 'o'. ('oe' <> 'ö').

Related:

• Creating a case-insensitive and accent/diacritics insensitive search on a field

• Finding the most commonly used non-stop words in a column

db<>fiddle here -- looking for 'Schoenstraße'

db<>fiddle here -- looking for 'Schönstraße'