{"id":28779,"date":"2023-10-25T08:51:30","date_gmt":"2023-10-25T06:51:30","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=28779"},"modified":"2023-10-25T08:51:32","modified_gmt":"2023-10-25T06:51:32","slug":"locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\/","title":{"rendered":"Locales, collations, encodings and the PostgreSQL sort order (2) \u2013 sorting strings"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">In the <a href=\"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\/\" target=\"_blank\" rel=\"noreferrer noopener\">last post<\/a> we&#8217;ve looked at how you can tell <a href=\"https:\/\/www.postgresql.org\/docs\/current\/app-initdb.html\" target=\"_blank\" rel=\"noreferrer noopener\">initdb<\/a> which character set and locale it should use for the initial databases. In this post we&#8217;ll look at how you can tell PostgreSQL how you want to have strings sorted.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Looking at my instance, this is what I currently have:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# \\l\n                                                        List of databases\n   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale  | ICU Rules |   Access privileges   \n-----------+----------+----------+-----------------+-------------+-------------+-------------+-----------+-----------------------\n postgres  | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu |           | \n template0 | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu |           | =c\/postgres          +\n           |          |          |                 |             |             |             |           | postgres=CTc\/postgres\n template1 | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu |           | =c\/postgres          +\n           |          |          |                 |             |             |             |           | postgres=CTc\/postgres\n(3 rows)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Checking how strings are sorted in this setup can easily be verified by creating a small table with a couple of rows:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3,5]; title: ; notranslate\" title=\"\">\npostgres=# create table t ( a text );\nCREATE TABLE\npostgres=# insert into t values (&#039;a&#039;),(&#039;A&#039;),(&#039;ii&#039;),(&#039;II&#039;),(&#039;*&#039;),(&#039;1.1&#039;),(&#039;-&#039;);\nINSERT 0 7\npostgres=# select * from t;\n  a  \n-----\n a\n A\n ii\n II\n *\n 1.1\n -\n(7 rows)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Sorting this right now gives the following result:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# select * from t order by 1;\n  a  \n-----\n -\n *\n 1.1\n a\n A\n ii\n II\n(7 rows)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Special characters come first, the number afterwards and characters last, lower case before upper case. What happens if we want to sort this with a Swiss\/German locale? As mentioned in the previous post: If you want to use the locales provided by glibc, then those need to be installed on the operating system:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3]; title: ; notranslate\" title=\"\">\npostgres=# \\! locale -a | grep de_CH\nde_CH.utf8\npostgres=# select * from t order by a collate &quot;de_CH.utf8&quot;;\n  a  \n-----\n *\n -\n 1.1\n a\n A\n ii\n II\n(7 rows)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">This gives exactly the same result. What about &#8220;C&#8221; or &#8220;POSIX&#8221;?<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# select * from t order by a collate &quot;POSIX&quot;;\n  a  \n-----\n *\n -\n 1.1\n A\n II\n a\n ii\n(7 rows)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Now upper case characters sort before lower characters and the order is not anymore the same. You can also use the ICU collations listed in <a href=\"https:\/\/www.postgresql.org\/docs\/current\/catalog-pg-collation.html\">pg_collation<\/a>:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# select * from t order by a collate &quot;de-BE-x-icu&quot;;\n  a  \n-----\n -\n *\n 1.1\n a\n A\n ii\n II\n(7 rows)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">To summarize this: Depending on how you want to have your strings sorted, you need to use the correct collation for this. Not all languages follow the same rules and if you want to support multiple languages things might become a bit more tricky.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the last post we&#8217;ve looked at how you can tell initdb which character set and locale it should use for the initial databases. In this post we&#8217;ll look at how you can tell PostgreSQL how you want to have strings sorted. Looking at my instance, this is what I currently have: Checking how strings [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[77],"type_dbi":[],"class_list":["post-28779","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Locales, collations, encodings and the PostgreSQL sort order (2) \u2013 sorting strings - 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\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Locales, collations, encodings and the PostgreSQL sort order (2) \u2013 sorting strings\" \/>\n<meta property=\"og:description\" content=\"In the last post we&#8217;ve looked at how you can tell initdb which character set and locale it should use for the initial databases. In this post we&#8217;ll look at how you can tell PostgreSQL how you want to have strings sorted. Looking at my instance, this is what I currently have: Checking how strings [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-10-25T06:51:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-25T06:51:32+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=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"Locales, collations, encodings and the PostgreSQL sort order (2) \u2013 sorting strings\",\"datePublished\":\"2023-10-25T06:51:30+00:00\",\"dateModified\":\"2023-10-25T06:51:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\\\/\"},\"wordCount\":230,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\\\/\",\"name\":\"Locales, collations, encodings and the PostgreSQL sort order (2) \u2013 sorting strings - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2023-10-25T06:51:30+00:00\",\"dateModified\":\"2023-10-25T06:51:32+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Locales, collations, encodings and the PostgreSQL sort order (2) \u2013 sorting strings\"}]},{\"@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":"Locales, collations, encodings and the PostgreSQL sort order (2) \u2013 sorting strings - 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\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\/","og_locale":"en_US","og_type":"article","og_title":"Locales, collations, encodings and the PostgreSQL sort order (2) \u2013 sorting strings","og_description":"In the last post we&#8217;ve looked at how you can tell initdb which character set and locale it should use for the initial databases. In this post we&#8217;ll look at how you can tell PostgreSQL how you want to have strings sorted. Looking at my instance, this is what I currently have: Checking how strings [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\/","og_site_name":"dbi Blog","article_published_time":"2023-10-25T06:51:30+00:00","article_modified_time":"2023-10-25T06:51:32+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"Locales, collations, encodings and the PostgreSQL sort order (2) \u2013 sorting strings","datePublished":"2023-10-25T06:51:30+00:00","dateModified":"2023-10-25T06:51:32+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\/"},"wordCount":230,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\/","url":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\/","name":"Locales, collations, encodings and the PostgreSQL sort order (2) \u2013 sorting strings - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-10-25T06:51:30+00:00","dateModified":"2023-10-25T06:51:32+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-2-sorting-strings\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Locales, collations, encodings and the PostgreSQL sort order (2) \u2013 sorting strings"}]},{"@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\/28779","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=28779"}],"version-history":[{"count":2,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/28779\/revisions"}],"predecessor-version":[{"id":28781,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/28779\/revisions\/28781"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=28779"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=28779"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=28779"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=28779"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}