Search for closest correct word in Postgres full text search

Question

I have a search-functionality in my app done with https://www.postgresql.org/docs/current/textsearch.html. I want to add the correction of spelling mistakes, in the fashion of "Did you mean ... ?".

So far I have seen that you can get the similarity of words with https://www.postgresql.org/docs/current/pgtrgm.html. This doesn't exactly match what I want, is I want a recommendation for the correction of a possible flawed input. Just getting the similarity assumes I would already know what correctly spelled word is meant.

Can Postgres do this? An example would be correcting "Borritoh" to "Burrito".

Answer 1

Just getting the similarity assumes I would already know what correctly spelled word is meant.

No, you just need a dictionary of recognized words. You then order your dictionary of recognized words by similarity or distance with a LIMIT 1.

If you build the trigram index of the GiST flavor, it will support ordering directly:

select word from dictionary ORDER BY word <-> 'Borritoh' limit 1

However, this will be pretty slow if none of the recognized words are similar. It might be better to use % to put a floor below which it just doesn't make any recommendation (which which will probably be better done with a GIN than GiST index, you should try it both ways).

select word from dictionary where word % 'Borritoh' ORDER BY word <-> 'Borritoh' limit 1