{"id":36761,"date":"2024-08-05T11:48:12","date_gmt":"2024-08-05T01:48:12","guid":{"rendered":"https:\/\/database.guide\/?p=36761"},"modified":"2024-08-05T11:48:14","modified_gmt":"2024-08-05T01:48:14","slug":"check-whether-a-unicode-string-is-normalized-in-postgresql","status":"publish","type":"post","link":"https:\/\/database.guide\/check-whether-a-unicode-string-is-normalized-in-postgresql\/","title":{"rendered":"Check Whether a Unicode String is Normalized in PostgreSQL"},"content":{"rendered":"\n

In PostgreSQL, we can use the ... IS NORMALIZED<\/code> expression to check whether or not a given Unicode string is in a specified Unicode normalization form.<\/p>\n\n\n\n

By default, it checks whether it’s in the NFC<\/code> form, but we also have the option of specifying NFD<\/code>, NFKC<\/code>, or NFKD<\/code>.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

text IS [NOT] [form] NORMALIZED<\/code><\/pre>\n\n\n\n
So we can use NOT<\/code> to negate the operation. We can also specify which normalization form to check for.<\/p>\n\n\n\n
The expression returns a boolean value (either true or false).<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s a quick example of how to use the expression:<\/p>\n\n\n\n
SELECT '\u00f6' IS NORMALIZED;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
t<\/pre>\n\n\n\n
In this case we get t<\/code> for true. In other words, the Unicode character is normalized in the NFC<\/code> form. We know it’s the NFC<\/code> form, because that’s the default.<\/p>\n\n\n\n
Here it is again, but this time we’ll check it for another form:<\/p>\n\n\n\n
SELECT '\u00f6' IS NFD NORMALIZED;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
f<\/pre>\n\n\n\n
This time we get f<\/code> for false. That’s because, although it’s normalized, it’s not normalized in the NFD<\/code> form.<\/p>\n\n\n\n
Negating the Operation<\/h2>\n\n\n\n
We can use NOT<\/code> to negate the operation:<\/p>\n\n\n\n
SELECT \n    '\u00f6' IS NORMALIZED AS \"Normalized\",\n    '\u00f6' IS NOT NORMALIZED AS \"Not Normalized\";<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
 Normalized | Not Normalized 
------------+----------------
 t          | f<\/pre>\n\n\n\n
Understanding Normalization<\/h2>\n\n\n\n
To get a proper understanding of how the ... IS NORMALIZED<\/code> expression works, we need to understand why we might need it.<\/p>\n\n\n\n
Take the following example:<\/p>\n\n\n\n
SELECT '\u00f6' = 'o\u0308';<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
f<\/pre>\n\n\n\n
Here, I compared two apparently identical characters, but the result indicates that they’re not identical (we got f<\/code> for false).<\/p>\n\n\n\n
The reason for this is because, although they are the same character, I actually used different Unicode code points to construct them. In my case I constructed them using HTML and then copied and pasted them into my psql<\/a><\/code> terminal.<\/p>\n\n\n\n
Many Unicode characters can be constructed from more than one set of code points, and that’s the case with the above character, known in Unicode as LATIN SMALL LETTER O WITH DIAERESIS<\/em>. This can be represented as a single code point (U+00F6<\/code>) or as two separate code points (U+006F<\/code> and U+0308<\/code>). Regarding the second one, U+006F<\/code> is the code point for LATIN SMALL LETTER O<\/em> and U+0308<\/code> is the code point for the COMBINING DIAERESIS<\/em>. These two code points are combined to form the same character that the single code point U+00F6<\/code> would produce.<\/p>\n\n\n\n
So given these are constructed with different code points, PostgreSQL sees them as not equal.<\/p>\n\n\n\n
So given they’re not equal, let’s check each character with the ... IS NORMALIZED<\/code> expression:<\/p>\n\n\n\n
SELECT \n    '\u00f6' IS NORMALIZED, \n    'o\u0308' IS NORMALIZED;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
 is_normalized | is_normalized 
---------------+---------------
 t             | f<\/pre>\n\n\n\n
So we can see that the first one is normalized in NFC<\/code> form but the second one isn’t (remember, NFC<\/code> is the default). <\/p>\n\n\n\n
Let’s specify a different form:<\/p>\n\n\n\n
SELECT \n    '\u00f6' IS NFD NORMALIZED, \n    'o\u0308' IS NFD NORMALIZED;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
 is_normalized | is_normalized 
---------------+---------------
 f             | t<\/pre>\n\n\n\n
This time we get the opposite result – the first column is false and the second column is true. <\/p>\n\n\n\n
If we want to avoid this confusion, we can use the normalize()<\/code> function<\/a> to normalize both characters using the same form. The default form for this function is NFC<\/code>, so we can do the following to normalize them in that form:<\/p>\n\n\n\n
SELECT \n    normalize('\u00f6') IS NORMALIZED,\n    normalize('o\u0308') IS NORMALIZED;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
 is_normalized | is_normalized 
---------------+---------------
 t             | t<\/pre>\n\n\n\n
This time both characters returned true when checking for normalization.<\/p>\n\n\n\n
We can specify a different form if required:<\/p>\n\n\n\n
SELECT \n    normalize('\u00f6', NFD) IS NFD NORMALIZED,\n    normalize('o\u0308', NFD) IS NFD NORMALIZED;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
 is_normalized | is_normalized 
---------------+---------------
 t             | t<\/pre>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
In PostgreSQL, we can use the … IS NORMALIZED expression to check whether or not a given Unicode string is in a specified Unicode normalization form. By default, it checks whether it’s in the NFC form, but we also have the option of specifying NFD, NFKC, or NFKD.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[10,84],"class_list":["post-36761","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-how-to","tag-string"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36761","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=36761"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36761\/revisions"}],"predecessor-version":[{"id":36776,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36761\/revisions\/36776"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=36761"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=36761"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=36761"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}