{"id":28686,"date":"2023-10-20T07:24:02","date_gmt":"2023-10-20T05:24:02","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=28686"},"modified":"2023-10-20T07:24:04","modified_gmt":"2023-10-20T05:24:04","slug":"locales-collations-encodings-and-the-postgresql-sort-order-1-basics","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\/","title":{"rendered":"Locales, collations, encodings and the PostgreSQL sort order (1) – basics"},"content":{"rendered":"\n

When it comes to sorting strings it is all about locales<\/a> and how PostgreSQL is using them. PostgreSQL uses the locales provided by the underlying operating system, by default this is “C” or “POSIX”. To see how that works lets do some simple demos to start with and then go a bit deeper.<\/p>\n\n\n\n

The system I am using here is a Debian 12<\/a>:<\/p>\n\n\n

\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] cat \/etc\/debian_version \n12.2\n<\/pre><\/div>\n\n\n
I’ve modified the current environment to not set any locale specific environment variables:<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] env | grep LC\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] \n<\/pre><\/div>\n\n\n
When we initialize a new PostgreSQL cluster from this environment, what do we get as locale and encoding settings in the new cluster?<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] initdb --pgdata=\/var\/tmp\/xx\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] export PGPORT=8888\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] pg_ctl --pgdata=\/var\/tmp\/xx --log=\/dev\/null start\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] psql -p 8888 -c "\\l"\n                                                   List of databases\n   Name    |  Owner   | Encoding  | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules |   Access privileges   \n-----------+----------+-----------+-----------------+---------+-------+------------+-----------+-----------------------\n postgres  | postgres | SQL_ASCII | libc            | C       | C     |            |           | \n template0 | postgres | SQL_ASCII | libc            | C       | C     |            |           | =c\/postgres          +\n           |          |           |                 |         |       |            |           | postgres=CTc\/postgres\n template1 | postgres | SQL_ASCII | libc            | C       | C     |            |           | =c\/postgres          +\n           |          |           |                 |         |       |            |           | postgres=CTc\/postgres\n(3 rows)\n<\/pre><\/div>\n\n\n
As nothing specific was defined in the environment and we did not specify anything with initdb, we got “C” as the locale and “SQL_ASCII” as the encoding<\/a>. Especially the encoding of “SQL_ASCII” is not a good choice as it is clearly stated in the documentation linked above: <\/p>\n\n\n\n
“The SQL_ASCII<\/code> setting behaves considerably differently from the other settings. When the server character set is SQL_ASCII<\/code>, the server interprets byte values 0\u2013127 according to the ASCII standard, while byte values 128\u2013255 are taken as uninterpreted characters. No encoding conversion will be done when the setting is SQL_ASCII<\/code>. Thus, this setting is not so much a declaration that a specific encoding is in use, as a declaration of ignorance about the encoding. In most cases, if you are working with any non-ASCII data, it is unwise to use the SQL_ASCII<\/code> setting because PostgreSQL will be unable to help you by converting or validating non-ASCII characters.”<\/p>\n\n\n\n
In other words, you should avoid that. How can you change the default encoding? There are two way of doing this. The first option is to properly set the locale settings in your environment:<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] export LANG=en_US.UTF-8\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] initdb --pgdata=\/var\/tmp\/yy\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] export PGPORT=8889\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] pg_ctl --pgdata=\/var\/tmp\/yy --log=\/dev\/null start\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] psql -p 8889 -c "\\l"\n                                                       List of databases\n   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges   \n-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------\n postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | \n template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c\/postgres          +\n           |          |          |                 |             |             |            |           | postgres=CTc\/postgres\n template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c\/postgres          +\n           |          |          |                 |             |             |            |           | postgres=CTc\/postgres\n(3 rows)\n<\/pre><\/div>\n\n\n
This will give you an encoding of UTF8<\/a> and a locale of en_US. The other option is to tell initdb<\/a> what you want to have:<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] initdb --pgdata=\/var\/tmp\/zz --locale="en_US.UTF8"\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] export PGPORT=8890\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] pg_ctl --pgdata=\/var\/tmp\/zz --log=\/dev\/null start\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] psql -p 8890 -c "\\l"\n                                                      List of databases\n   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   \n-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------\n postgres  | postgres | UTF8     | libc            | en_US.UTF8 | en_US.UTF8 |            |           | \n template0 | postgres | UTF8     | libc            | en_US.UTF8 | en_US.UTF8 |            |           | =c\/postgres          +\n           |          |          |                 |            |            |            |           | postgres=CTc\/postgres\n template1 | postgres | UTF8     | libc            | en_US.UTF8 | en_US.UTF8 |            |           | =c\/postgres          +\n           |          |          |                 |            |            |            |           | postgres=CTc\/postgres\n(3 rows)\n<\/pre><\/div>\n\n\n
This gives you the exact same thing. If you want to use your native locale, you can do that as well:<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] initdb --pgdata=\/var\/tmp\/oo --locale="de_CH.UTF8"\nThe files belonging to this database system will be owned by user "postgres".\nThis user must also own the server process.\n\ninitdb: error: invalid locale name "de_CH.UTF8"\ninitdb: hint: If the locale name is specific to ICU, use --icu-locale.\n<\/pre><\/div>\n\n\n
What is the issue here? The answer is quite simple: This locale does not exist on the operating system:<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] locale -a\nC\nC.utf8\nen_US.utf8\nPOSIX\n<\/pre><\/div>\n\n\n
On Debian this needs to be generated by enabling the locale in \/etc\/locale.gen and then run “locale-gen” afterwards:<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] sudo vi \/etc\/locale.gen \npostgres@debian12-pg:\/home\/postgres\/ [pgdev] grep de_CH \/etc\/locale.gen \n# de_CH ISO-8859-1\nde_CH.UTF-8 UTF-8\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] sudo locale-gen\nGenerating locales (this might take a while)...\n  de_CH.UTF-8... done\n  en_US.UTF-8... done\nGeneration complete.\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] locale -a\nC\nC.utf8\nde_CH.utf8\nen_US.utf8\nPOSIX\n<\/pre><\/div>\n\n\n
Now, that the new locale is there, it can also be given to initdb (or specified in the environment):<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] initdb --pgdata=\/var\/tmp\/oo --locale="de_CH.UTF8"\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] export PGPORT=8891\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] pg_ctl --pgdata=\/var\/tmp\/oo --log=\/dev\/null start\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] psql -p 8891 -c "\\l"\n                                                      List of databases\n   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   \n-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------\n postgres  | postgres | UTF8     | libc            | de_CH.UTF8 | de_CH.UTF8 |            |           | \n template0 | postgres | UTF8     | libc            | de_CH.UTF8 | de_CH.UTF8 |            |           | =c\/postgres          +\n           |          |          |                 |            |            |            |           | postgres=CTc\/postgres\n template1 | postgres | UTF8     | libc            | de_CH.UTF8 | de_CH.UTF8 |            |           | =c\/postgres          +\n           |          |          |                 |            |            |            |           | postgres=CTc\/postgres\n(3 rows)\n<\/pre><\/div>\n\n\n
If you don’t want to use the locales from glibc you can also use ICU<\/a> (International Components for Unicode). When your version of PostgreSQL is compiled with support for ICU and it is installed on your system then this is another choice you have. On Debian 12 it is this:<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] apt info libicu72\nPackage: libicu72\nVersion: 72.1-3\nPriority: optional\nSection: libs\nSource: icu\nMaintainer: Laszlo Boszormenyi (GCS) gcs@debian.org\nInstalled-Size: 37.0 MB\nDepends: libc6 (>= 2.34), libgcc-s1 (>= 3.0), libstdc++6 (>= 12)\nBreaks: libiculx63 (<< 63.1-5), openttd (<< 1.8.0-2~)\nReplaces: libiculx63 (<< 63.1-5)\nHomepage: https:\/\/icu.unicode.org\/\nTag: role::shared-lib\nDownload-Size: 9,376 kB\nAPT-Manual-Installed: no\nAPT-Sources: http:\/\/deb.debian.org\/debian bookworm\/main amd64 Packages\nDescription: International Components for Unicode\nICU is a C++ and C library that provides robust and full-featured\nUnicode and locale support. This package contains the runtime\nlibraries for ICU.\n<\/pre><\/div>\n\n\n
For using this you need to switch the locale provider when you are initializing a new cluster:<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] initdb --pgdata=\/var\/tmp\/qq --locale-provider=icu --icu-locale=en-US-x-icu\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] export PGPORT=8892\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] pg_ctl --pgdata=\/var\/tmp\/qq --log=\/dev\/null start\n postgres@debian12-pg:\/home\/postgres\/ [pgdev] psql -p 8892 -c "\\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\n<\/pre><\/div>\n\n\n
Once a cluster is running you may ask PostgreSQL what is available:<\/p>\n\n\n
\npostgres=# select * from pg_collation ;\n  oid  |        collname        | collnamespace | collowner | collprovider | collisdeterministic | collencoding | collcollate | collctype  |  colliculocale   | collicurules | collversion \n-------+------------------------+---------------+-----------+--------------+---------------------+--------------+-------------+------------+------------------+--------------+-------------\n   100 | default                |            11 |        10 | d            | t                   |           -1 |             |            |                  |              | \n   950 | C                      |            11 |        10 | c            | t                   |           -1 | C           | C          |                  |              | \n   951 | POSIX                  |            11 |        10 | c            | t                   |           -1 | POSIX       | POSIX      |                  |              | \n   962 | ucs_basic              |            11 |        10 | c            | t                   |            6 | C           | C          |                  |              | \n   963 | unicode                |            11 |        10 | i            | t                   |           -1 |             |            | und              |              | 153.120\n 12348 | C.utf8                 |            11 |        10 | c            | t                   |            6 | C.utf8      | C.utf8     |                  |              | \n 12349 | de_CH.utf8             |            11 |        10 | c            | t                   |            6 | de_CH.utf8  | de_CH.utf8 |                  |              | 2.36\n 12350 | en_US.utf8             |            11 |        10 | c            | t                   |            6 | en_US.utf8  | en_US.utf8 |                  |              | 2.36\n 12351 | de_CH                  |            11 |        10 | c            | t                   |            6 | de_CH.utf8  | de_CH.utf8 |                  |              | 2.36\n 12352 | en_US                  |            11 |        10 | c            | t                   |            6 | en_US.utf8  | en_US.utf8 |                  |              | 2.36\n 12353 | und-x-icu              |            11 |        10 | i            | t                   |           -1 |             |            | und              |              | 153.120\n 12354 | af-x-icu               |            11 |        10 | i            | t                   |           -1 |             |            | af               |              | 153.120.42\n 12355 | af-NA-x-icu            |            11 |        10 | i            | t                   |           -1 |             |            | af-NA            |              | 153.120.42\n 12356 | af-ZA-x-icu            |            11 |        10 | i            | t                   |           -1 |             |            | af-ZA            |              | 153.120.42\n<\/pre><\/div>\n\n\n
That’s it for now. In one of the next posts we’ll look into the sort order.<\/p>\n","protected":false},"excerpt":{"rendered":"
When it comes to sorting strings it is all about locales and how PostgreSQL is using them. PostgreSQL uses the locales provided by the underlying operating system, by default this is “C” or “POSIX”. To see how that works lets do some simple demos to start with and then go a bit deeper. The system […]<\/p>\n","protected":false},"author":29,"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":[2749],"class_list":["post-28686","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql","type-postgresql"],"acf":[],"yoast_head":"\nLocales, collations, encodings and the PostgreSQL sort order (1) - basics - 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-1-basics\/\" \/>\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 (1) - basics\" \/>\n<meta property=\"og:description\" content=\"When it comes to sorting strings it is all about locales and how PostgreSQL is using them. PostgreSQL uses the locales provided by the underlying operating system, by default this is “C” or “POSIX”. To see how that works lets do some simple demos to start with and then go a bit deeper. The system […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-10-20T05:24:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-20T05:24:04+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 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\\\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Locales, collations, encodings and the PostgreSQL sort order (1) – basics\",\"datePublished\":\"2023-10-20T05:24:02+00:00\",\"dateModified\":\"2023-10-20T05:24:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\\\/\"},\"wordCount\":493,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & 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-1-basics\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\\\/\",\"name\":\"Locales, collations, encodings and the PostgreSQL sort order (1) - basics - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2023-10-20T05:24:02+00:00\",\"dateModified\":\"2023-10-20T05:24:04+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\\\/#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 (1) – basics\"}]},{\"@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\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Locales, collations, encodings and the PostgreSQL sort order (1) - basics - 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-1-basics\/","og_locale":"en_US","og_type":"article","og_title":"Locales, collations, encodings and the PostgreSQL sort order (1) - basics","og_description":"When it comes to sorting strings it is all about locales and how PostgreSQL is using them. PostgreSQL uses the locales provided by the underlying operating system, by default this is “C” or “POSIX”. To see how that works lets do some simple demos to start with and then go a bit deeper. The system […]","og_url":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\/","og_site_name":"dbi Blog","article_published_time":"2023-10-20T05:24:02+00:00","article_modified_time":"2023-10-20T05:24:04+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Locales, collations, encodings and the PostgreSQL sort order (1) – basics","datePublished":"2023-10-20T05:24:02+00:00","dateModified":"2023-10-20T05:24:04+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\/"},"wordCount":493,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & 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-1-basics\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\/","url":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\/","name":"Locales, collations, encodings and the PostgreSQL sort order (1) - basics - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-10-20T05:24:02+00:00","dateModified":"2023-10-20T05:24:04+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/locales-collations-encodings-and-the-postgresql-sort-order-1-basics\/#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 (1) – basics"}]},{"@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\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/28686","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\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=28686"}],"version-history":[{"count":13,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/28686\/revisions"}],"predecessor-version":[{"id":28699,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/28686\/revisions\/28699"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=28686"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=28686"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=28686"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=28686"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}