{"id":15493,"date":"2021-01-08T06:30:49","date_gmt":"2021-01-08T05:30:49","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/"},"modified":"2021-01-08T06:30:49","modified_gmt":"2021-01-08T05:30:49","slug":"dealing-with-german-umlaute-in-postgresqls-full-text-search","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/","title":{"rendered":"Dealing with German “Umlaute” in PostgreSQL’s full text search"},"content":{"rendered":"

PostgreSQL comes with build-in Full Text Search<\/a> and you can do quite amazing stuff with it. A question that popped up during one of the last PostgreSQL for developers workshop<\/a> was: How can I deal with German “Umlaute” such as “\u00e4”, “\u00f6” and “\u00fc” in such a way, that I can search for e.g. “Schn\u00f6sel” and the result will give me “Schn\u00f6sel” as well as “Schnoesel”? One way to deal with that would be to replace all occurrences of “\u00e4”, “\u00f6” and “\u00fc” with “ae”, “oe” and “ue” before doing the search, and then automatically convert the search string to “ae”, “oe” and “ue”, once a search with an “Umlaut” comes in. Even if this can be done quite easily (but probably introduces other issues), there is a more elegant solution to that with PostreSQL’s build-in Full Text Search.<\/p>\n

<\/p>\n

Lets start with a simple table containing two strings:<\/p>\n

\npostgres=# create table umlaute ( string text );\nCREATE TABLE\npostgres=# insert into umlaute values ('Schn\u00f6sel');\nINSERT 0 1\npostgres=# insert into umlaute values ('Schnoesel');\nINSERT 0 1\npostgres=# select * from umlaute;\n  string   \n-----------\n Schn\u00f6sel\n Schnoesel\n(2 rows)\n<\/pre>\n
If you ask for the two variations of the string in the default configuration, you only get the exact match:<\/p>\n
\npostgres=# select * from umlaute where to_tsvector(string) @@ to_tsquery('Schn\u00f6sel');\n  string  \n----------\n Schn\u00f6sel\n(1 row)\n\npostgres=# select * from umlaute where to_tsvector(string) @@ to_tsquery('Schnoesel');\n  string   \n-----------\n Schnoesel\n(1 row)\n<\/pre>\n
By default PostgreSQL uses the English text search configuration: <\/p>\n
\npostgres=# show default_text_search_config;\n default_text_search_config \n----------------------------\n pg_catalog.english\n(1 row)\n<\/pre>\n
… and you might think that switching this to “German” already resolves the issue:<\/p>\n
\npostgres=# set default_text_search_config='german';\nSET\npostgres=# select current_setting('default_text_search_config');\n  current_setting  \n-------------------\n pg_catalog.german\n(1 row)\n\npostgres=# select * from umlaute where to_tsvector(string) @@ to_tsquery('Schn\u00f6sel');\n  string  \n----------\n Schn\u00f6sel\n(1 row)\n\npostgres=# select * from umlaute where to_tsvector(string) @@ to_tsquery('Schnoesel');\n  string   \n-----------\n Schnoesel\n(1 row)\n<\/pre>\n
This is definitely not the case, so more work needs to be done to get this working. PostgreSQL allows you to create your own text search configurations easily, and this is what happens in the next step:<\/p>\n
\npostgres=# create text search configuration my_de ( copy = german );\nCREATE TEXT SEARCH CONFIGURATION\n<\/pre>\n
We now have a copy of the default German text search configuration available as “my_de”. To test, that this actually works you can explicitly specify this configuration in our text search (although, it of course does not change anything right now):<\/p>\n
\npostgres=# select * from umlaute where to_tsvector('my_de',string) @@ to_tsquery('my_de','Schn\u00f6sel');\n  string  \n----------\n Schn\u00f6sel\n(1 row)\n\npostgres=# select * from umlaute where to_tsvector('my_de',string) @@ to_tsquery('my_de','Schnoesel');\n  string   \n-----------\n Schnoesel\n(1 row)\n<\/pre>\n
Before we can alter our new text search configuration any further we need the extension unaccent<\/a>. This extension “removes accents (diacritic signs) from lexemes”. Here is a little example of what it does:<\/p>\n
\npostgres=# create extension unaccent;\nCREATE EXTENSION\npostgres=# select unaccent ('aa\u00c4');\n unaccent \n----------\n aaA\n(1 row)\n\npostgres=# select unaccent ('\u00c6\u00c6\u00c6');\n unaccent \n----------\n AEAEAE\n(1 row)\n<\/pre>\n
Having that in place we can modify the text search configuration we just created:<\/p>\n
\npostgres=# ALTER TEXT SEARCH CONFIGURATION my_de ALTER MAPPING FOR hword, hword_part, word WITH unaccent, german_stem;\nALTER TEXT SEARCH CONFIGURATION\n<\/pre>\n
If you wonder, what “hword”, “word” and “hword_part” actually mean, then have a look here<\/a>. We basically instructed our text search configuration to “unaccent” our search terms by default. Is that enough?<\/p>\n
\npostgres=# select * from umlaute where to_tsvector('my_de',string) @@ to_tsquery('my_de','Schn\u00f6sel');\n  string  \n----------\n Schn\u00f6sel\n(1 row)\n\npostgres=# select * from umlaute where to_tsvector('my_de',string) @@ to_tsquery('my_de','Schnoesel');\n  string   \n-----------\n Schnoesel\n(1 row)\n<\/pre>\n
No, we still get the same results, but why? The “unaccent” extension uses a rules file to do the translation of the specific characters. By default the “unaccent.rules” file is used and you can find that in the SHAREDIR of your PostgreSQL installation:<\/p>\n
\npostgres=# ! pg_config | grep SHAREDIR\nSHAREDIR = \/u01\/app\/postgres\/product\/DEV\/db_1\/share\npostgres=# ! ls -la \/u01\/app\/postgres\/product\/DEV\/db_1\/share\/tsearch_data\/*.rules\n-rw-r--r-- 1 postgres postgres 9549 Dec 26 15:45 \/u01\/app\/postgres\/product\/DEV\/db_1\/share\/tsearch_data\/unaccent.rules\n-rw-r--r-- 1 postgres postgres  139 Dec 26 15:44 \/u01\/app\/postgres\/product\/DEV\/db_1\/share\/tsearch_data\/xsyn_sample.rules\n<\/pre>\n
This file contains the rules for translating special characters to their desired target:<\/p>\n
\npostgres=# ! head \/u01\/app\/postgres\/product\/DEV\/db_1\/share\/tsearch_data\/unaccent.rules\n\u00a9       (C)\n\u00ab       <>\n\u00bc        1\/4\n\u00bd        1\/2\n\u00be        3\/4\n\u00c0       A\n\u00c1       A\n<\/pre>\n
If you look for the German “Umlaute” you’ll get this:<\/p>\n
\npostgres=# ! egrep \"\u00e4|\u00f6|\u00fc\" \/u01\/app\/postgres\/product\/DEV\/db_1\/share\/tsearch_data\/unaccent.rules\n\u00e4       a\n\u00f6       o\n\u00fc       u\n<\/pre>\n
This is the reason why we still do not get the desired result. I’ll ge ahead and modify the rules directly but you should rather create your own rules file before modifying an existing one:<\/p>\n
\npostgres=# ! egrep \"\u00e4|\u00f6|\u00fc\" \/u01\/app\/postgres\/product\/DEV\/db_1\/share\/tsearch_data\/unaccent.rules\n\u00e4       ae\n\u00f6       oe\n\u00fc       ue\n<\/pre>\n
From now on “\u00e4” will be translated to “ae”, “\u00fc” to “ue” and “\u00f6” to “oe” and our search works as expected:<\/p>\n
\npostgres=# select * from umlaute where to_tsvector('my_de',string) @@ to_tsquery('my_de','Schnoesel');\n  string   \n-----------\n Schn\u00f6sel\n Schnoesel\n(2 rows)\n\npostgres=# select * from umlaute where to_tsvector('my_de',string) @@ to_tsquery('my_de','Schn\u00f6sel');\n  string   \n-----------\n Schn\u00f6sel\n Schnoesel\n(2 rows)\n<\/pre>\n
Great stuff.<\/p>\n","protected":false},"excerpt":{"rendered":"
PostgreSQL comes with build-in Full Text Search and you can do quite amazing stuff with it. A question that popped up during one of the last PostgreSQL for developers workshop was: How can I deal with German “Umlaute” such as “\u00e4”, “\u00f6” and “\u00fc” in such a way, that I can search for e.g. “Schn\u00f6sel” […]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-15493","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nDealing with German "Umlaute" in PostgreSQL's full text search - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dealing with German "Umlaute" in PostgreSQL's full text search\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL comes with build-in Full Text Search and you can do quite amazing stuff with it. A question that popped up during one of the last PostgreSQL for developers workshop was: How can I deal with German “Umlaute” such as “\u00e4”, “\u00f6” and “\u00fc” in such a way, that I can search for e.g. “Schn\u00f6sel” […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-01-08T05:30:49+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dealing-with-german-umlaute-in-postgresqls-full-text-search\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dealing-with-german-umlaute-in-postgresqls-full-text-search\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"Dealing with German “Umlaute” in PostgreSQL’s full text search\",\"datePublished\":\"2021-01-08T05:30:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dealing-with-german-umlaute-in-postgresqls-full-text-search\\\/\"},\"wordCount\":482,\"commentCount\":1,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dealing-with-german-umlaute-in-postgresqls-full-text-search\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dealing-with-german-umlaute-in-postgresqls-full-text-search\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dealing-with-german-umlaute-in-postgresqls-full-text-search\\\/\",\"name\":\"Dealing with German \\\"Umlaute\\\" in PostgreSQL's full text search - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2021-01-08T05:30:49+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dealing-with-german-umlaute-in-postgresqls-full-text-search\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dealing-with-german-umlaute-in-postgresqls-full-text-search\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/dealing-with-german-umlaute-in-postgresqls-full-text-search\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dealing with German “Umlaute” in PostgreSQL’s full text search\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Dealing with German \"Umlaute\" in PostgreSQL's full text search - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/","og_locale":"en_US","og_type":"article","og_title":"Dealing with German \"Umlaute\" in PostgreSQL's full text search","og_description":"PostgreSQL comes with build-in Full Text Search and you can do quite amazing stuff with it. A question that popped up during one of the last PostgreSQL for developers workshop was: How can I deal with German “Umlaute” such as “\u00e4”, “\u00f6” and “\u00fc” in such a way, that I can search for e.g. “Schn\u00f6sel” […]","og_url":"https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/","og_site_name":"dbi Blog","article_published_time":"2021-01-08T05:30:49+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"Dealing with German “Umlaute” in PostgreSQL’s full text search","datePublished":"2021-01-08T05:30:49+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/"},"wordCount":482,"commentCount":1,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/","url":"https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/","name":"Dealing with German \"Umlaute\" in PostgreSQL's full text search - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-01-08T05:30:49+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/dealing-with-german-umlaute-in-postgresqls-full-text-search\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Dealing with German “Umlaute” in PostgreSQL’s full text search"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15493","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=15493"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15493\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=15493"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=15493"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=15493"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=15493"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}