Finding Diacritics in a Postgres Table
Tags: postgres, java, database
Published: 2020-03-20 13:39:05

<!-- wp:paragraph -->
<p>Recently I had the task to find all the Diacritics in a database table. I had to find an easy way to select every row that has a diacritic in the name. The solution was surprisingly simple, but a bit unintuitive. In this article, I will provide you with that solution as well as additional information about how Java and Postgres treat diacritics and other characters.</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2>What is a diacritic?</h2>
<!-- /wp:heading -->

<!-- wp:image {"id":5037,"align":"center"} -->
<div class="wp-block-image"><figure class="aligncenter"><img src="https://tenmilesquare.com/wp-content/uploads/2020/01/diacriti.jpg" alt="" class="wp-image-5037"/></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>A Diacritic is a mark or an accent on a letter that indicates that the letter should be pronounced differently. Diacritics are often referred to as accents. The marked letter and the un-marked letter are logically the same letters in the language. Examples of Diacritics are</p>
<!-- /wp:paragraph -->

<!-- wp:preformatted -->
<pre class="wp-block-preformatted">áàâüñçå</pre>
<!-- /wp:preformatted -->

<!-- wp:paragraph -->
<p>It is important to understand that some characters that we might consider diacritics are not actually diacritics. This is because, In the character's respective alphabet, they are individual letters.  Examples are</p>
<!-- /wp:paragraph -->

<!-- wp:preformatted -->
<pre class="wp-block-preformatted">Æ æ Ø ø Ł ł</pre>
<!-- /wp:preformatted -->

<!-- wp:paragraph -->
<p>When dealing with diacritics its important to understand how the system will treat certain characters. </p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":5038,"align":"center","width":419,"height":527} -->
<div class="wp-block-image"><figure class="aligncenter is-resized"><img src="https://tenmilesquare.com/wp-content/uploads/2020/01/Homer_British_Museum.jpg" alt="" class="wp-image-5038" width="419" height="527"/></figure></div>
<!-- /wp:image -->

<!-- wp:heading -->
<h2>How does Java deal with Diacritics?</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Java handles diacritics in a strict fashion. For these examples, we will use the Normalizer.normalize utility in the Apache Commons-text library. We will see that "Æ æ Ø ø Ł ł",  are not replaced with a similar English analog. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>We will use the following method for testing</p>
<!-- /wp:paragraph -->

<!-- wp:preformatted {"className":"highlight code highlighter"} -->
<pre class="wp-block-preformatted highlight code highlighter"><strong>public static </strong>String stripDiacritics(String input){<br>    <strong>if </strong>( !Normalizer.<em>isNormalized</em>(input, Normalizer.Form.<strong><em>NFD</em></strong>) ) {<br>        <strong>return </strong>Normalizer.<em>normalize</em>(input, Normalizer.Form.<strong><em>NFD</em></strong>).replaceAll(<strong>"</strong><strong>\\</strong><strong>p{M}+"</strong>, <strong>""</strong>);<br>    }<br>    <strong>return </strong>input;<br>}</pre>
<!-- /wp:preformatted -->

<!-- wp:paragraph -->
<p>Testing this method will result in the following</p>
<!-- /wp:paragraph -->

<!-- wp:preformatted {"className":"highlight code highlighter"} -->
<pre class="wp-block-preformatted highlight code highlighter"> @Test<br><strong>public void </strong>testStripDiacritic(){<br>    <em>assertEquals</em>(<strong>"aabbsser3ssa35ds"</strong>, DiacriticUtility.<em>stripDiacritics</em>(<strong>"aabbsser3ssa35ds"</strong>));<br>    <em>assertEquals</em>(<strong>"This is a funky String"</strong>, DiacriticUtility.<em>stripDiacritics</em>(<strong>"Tĥïŝ ĩš â fůňķŷ Šťŕĭńġ"</strong>));<br>    <em>assertEquals</em>(<strong>"Ø or Ł"</strong>, DiacriticUtility.<em>stripDiacritics</em>(<strong>"Ø or Ł"</strong>));<br>    <em>assertEquals</em>(<strong>"the German umlauts: a, o, u"</strong>, DiacriticUtility.<em>stripDiacritics</em>(<strong>"the German umlauts: ä, ö, ü"</strong>));<br>    <em>assertEquals</em>(<strong>"manana"</strong>, DiacriticUtility.<em>stripDiacritics</em>(<strong>"mañana"</strong>));<br>    <em>assertEquals</em>(<strong>"Æ æ Æ æ or Æ æ"</strong>, DiacriticUtility.<em>stripDiacritics</em>(<strong>"Ǣ ǣ Ǽ ǽ or Æ æ"</strong>));<br>}</pre>
<!-- /wp:preformatted -->

<!-- wp:paragraph -->
<p>Notice diacritics such as "ůňķŷ" are replaced with "unky", but "Æ æ" are not replaced with "AE ae"</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2>How does Postgres handle diacritics?</h2>
<!-- /wp:heading -->

<!-- wp:image {"id":5039,"align":"center","width":224,"height":237} -->
<div class="wp-block-image"><figure class="aligncenter is-resized"><img src="https://tenmilesquare.com/wp-content/uploads/2020/01/database.jpg" alt="" class="wp-image-5039" width="224" height="237"/></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Postgres treats diacritics the same as Java but has a different effect on the other characters. In Postgres, we use the <em>unaccent</em> extension.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>To install <em>unccent</em> run the following.</p>
<!-- /wp:paragraph -->

<!-- wp:preformatted {"className":"highlight code highlighter"} -->
<pre class="wp-block-preformatted highlight code highlighter"><strong>CREATE EXTENSION </strong>unaccent;<br></pre>
<!-- /wp:preformatted -->

<!-- wp:paragraph -->
<p>Now let's run a simple query to see how characters are treated.</p>
<!-- /wp:paragraph -->

<!-- wp:preformatted {"className":"highlight code highlighter"} -->
<pre class="wp-block-preformatted highlight code highlighter"><strong>select </strong><em>unaccent</em>(<strong>'Tĥïŝ ĩš â fůňķŷ Šťŕĭńġ | łŁØø | ä, ö, ü | Ǣ ǣ Ǽ ǽ æ or Æ æ'</strong>) <br><strong>FROM </strong>blog.diacritic_blog <strong>LIMIT</strong>1;</pre>
<!-- /wp:preformatted -->

<!-- wp:paragraph -->
<p><strong>Output:</strong></p>
<!-- /wp:paragraph -->

<!-- wp:preformatted {"className":"highlight code highlighter"} -->
<pre class="wp-block-preformatted highlight code highlighter">This is a funky String | lLOo | a, o, u | Ǣ ǣ Ǽ ǽ ae or AE ae</pre>
<!-- /wp:preformatted -->

<!-- wp:image {"id":5040,"align":"center","width":470,"height":263} -->
<div class="wp-block-image"><figure class="aligncenter is-resized"><img src="https://tenmilesquare.com/wp-content/uploads/2020/01/picard-facepalm.jpg" alt="" class="wp-image-5040" width="470" height="263"/></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Notice that the expected diacritics are resolved, but so is "Æ æ ł Ł Ø ø". Interestingly enough is the Postgres's unaccent does not resolve "Ǣ ǣ Ǽ ǽ" to "Æ æ Æ æ" or to "AE ae AE as".</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2>Finding diacritics in a Postgres Table?</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>As we saw above there are a lot of special cases regarding how Postgres will handle diacritics and special characters. This solution to finding the rows with diacritics will not take those edge cases into account but will give you a basic solution to querying the table for diacritics. You can add regex and additional where clauses to the query as needed.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>This example will use the following</p>
<!-- /wp:paragraph -->

<!-- wp:preformatted {"className":"highlight code highlighter"} -->
<pre class="wp-block-preformatted highlight code highlighter"><strong>CREATE TABLE </strong>blog.diacritic_blog(<br>  <strong>id bigserial NOT NULL</strong>,<br>  <strong>name varchar</strong>(255) <strong>NOT NULL</strong>,<br>  <strong>CONSTRAINT </strong>pk_diacritic_id <strong>PRIMARY KEY </strong>(<strong>id</strong>)<br>);<br><br><strong>INSERT INTO </strong>blog.diacritic_blog(<strong>name</strong>) <strong>values </strong>(<strong>'aabbsser3ssa35ds'</strong>);<br><strong>INSERT INTO </strong>blog.diacritic_blog(<strong>name</strong>) <strong>values </strong>(<strong>'Tĥïŝ ĩš â fůňķŷ Šťŕĭńġ'</strong>);<br><strong>INSERT INTO </strong>blog.diacritic_blog(<strong>name</strong>) <strong>values </strong>(<strong>'Ø or Ł or łŁØø'</strong>);<br><strong>INSERT INTO </strong>blog.diacritic_blog(<strong>name</strong>) <strong>values </strong>(<strong>'the German umlauts: ä, ö, ü'</strong>);<br><strong>INSERT INTO </strong>blog.diacritic_blog(<strong>name</strong>) <strong>values </strong>(<strong>'mañana'</strong>);<br><strong>INSERT INTO </strong>blog.diacritic_blog(<strong>name</strong>) <strong>values </strong>(<strong>'Ǣ ǣ Ǽ ǽ æ or Æ æ'</strong>);</pre>
<!-- /wp:preformatted -->

<!-- wp:paragraph -->
<p>The following query will return 5 rows:</p>
<!-- /wp:paragraph -->

<!-- wp:preformatted {"className":"highlight code highlighter"} -->
<pre class="wp-block-preformatted highlight code highlighter"><strong>SELECT name, unaccent(name)</strong><em> </em><strong>FROM </strong>blog.diacritic_blog <strong>where name </strong>!= <em>unaccent</em>(<strong>name</strong>);</pre>
<!-- /wp:preformatted -->

<!-- wp:paragraph -->
<p><strong>OUTPUT:</strong></p>
<!-- /wp:paragraph -->

<!-- wp:table -->
<table class="wp-block-table"><tbody><tr><td>Tĥïŝ ĩš â fůňķŷ Šťŕĭńġ</td><td>This is a funky String</td></tr><tr><td>Ø or Ł or łŁØø</td><td>O or L or lLOo</td></tr><tr><td>the German umlauts: ä, ö, ü</td><td>the German umlauts: a, o, u</td></tr><tr><td>mañana</td><td>manana</td></tr><tr><td>Ǣ ǣ Ǽ ǽ æ or Æ æ</td><td>Ǣ ǣ Ǽ ǽ ae or AE ae</td></tr></tbody></table>
<!-- /wp:table -->

<!-- wp:heading -->
<h2>Summary</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Finding all the Diacritics in a Postgres table is simple if not a bit unintuitive. However, handling diacritics and non-English alphabet characters is a minefield. Hopefully, now you have a basic understanding of diacritics and how Java and Postgres treat these characters. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>If you'd like to see a complete treatment of how other languages (JavaScript or Python) treats these character please reach out to me via Twitter <a href="https://twitter.com/ryboflavin42">@ryboflavin42</a> and let me know what other languages you'd like to see.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>The examples from this article can be found on GitHub at <a href="https://github.com/djchi82/DiacriticBlogProject">https://github.com/djchi82/DiacriticBlogProject</a>.</p>
<!-- /wp:paragraph -->