{"id":13606,"date":"2020-03-05T16:44:31","date_gmt":"2020-03-05T15:44:31","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/"},"modified":"2020-03-05T16:44:31","modified_gmt":"2020-03-05T15:44:31","slug":"collations-in-postgresql-basics","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/","title":{"rendered":"Collations in PostgreSQL – basics"},"content":{"rendered":"

When you work with databases sooner or later you will need to get in touch witch collations<\/a>. It might be that you never needed to think about it as initdb<\/a> picked a value that just worked for you and nobody in your database ever tried to use a collation that is not available in your installation. Once someone is trying that or there is the requirement to use a specific collation from the beginning you need to know at least the basics and this is the topic of this post. In contrast to Oracle, PostgreSQL relies on the operating system for collation support. As always, a demo says more than thousands words, so lets go.<\/p>\n

<\/p>\n

I am running CentOS<\/a> 8 (8.1.1911 (Core)), minimal installation plus the packages required for installing PostgreSQL from source code. To get a default environment lets create brand new user:<\/p>\n

\npostgres@centos8pg:\/home\/postgres\/ [pgdev] sudo useradd pg\npostgres@centos8pg:\/home\/postgres\/ [pgdev] sudo su - pg\n<\/pre>\n
Once we switch to that user and check the LANG and LC_* variable this is what we get:<\/p>\n
\n[pg@centos8pg ~]$ env | grep LC\n[pg@centos8pg ~]$ env | grep LANG\nLANG=en_US.UTF-8\n<\/pre>\n
When we run initdb to create a new cluster this is what gets picked up and will become the default for the databases:<\/p>\n
\n[pg@centos8pg ~]$ \/u01\/app\/postgres\/product\/12\/db_0\/bin\/initdb -D \/var\/tmp\/pg\n[pg@centos8pg ~]$ export PGPORT=9999\n[pg@centos8pg ~]$ \/u01\/app\/postgres\/product\/12\/db_0\/bin\/pg_ctl -D \/var\/tmp\/pg start\n[pg@centos8pg ~]$ \/u01\/app\/postgres\/product\/12\/db_0\/bin\/psql -p 9999 postgres\npsql (12.0)\nType \"help\" for help.\n\npostgres=# l\n                              List of databases\n   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges \n-----------+-------+----------+-------------+-------------+-------------------\n postgres  | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n template0 | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/pg            +\n           |       |          |             |             | pg=CTc\/pg\n template1 | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/pg            +\n           |       |          |             |             | pg=CTc\/pg\n(3 rows)\n<\/pre>\n
With PostgreSQL you can define the default collation for a database at the time you create a database so creating a new database with the same collation as the ones already existing is not an issue at all:<\/p>\n
\n[pg@centos8pg ~]$ \/u01\/app\/postgres\/product\/12\/db_0\/bin\/psql -p 9999 postgres\npostgres=# create database tmp with LC_COLLATE = \"en_US.UTF-8\";\nCREATE DATABASE\n<\/pre>\n
The trouble starts when you want to use a collation that is not currently available from the operating system:<\/p>\n
\npostgres=# create database tmp2 with LC_COLLATE = \"de_DE.UTF-8\";\nERROR:  invalid locale name: \"de_DE.UTF-8\"\n<\/pre>\n
At this point PostgreSQL can not create the database for you as it does not know anything about the German locale specified in the create database command. Apparently my CentOS installation does not have support for this collation. How then can you check what the operating system currently provides? The command to use on Linux flavors that use systemd<\/a> is localectl<\/a>:<\/p>\n
\n[pg@centos8pg ~]$ localectl list-locales\nC.utf8\nen_AG\nen_AU\nen_AU.utf8\nen_BW\nen_BW.utf8\nen_CA\nen_CA.utf8\nen_DK\nen_DK.utf8\nen_GB\nen_GB.iso885915\nen_GB.utf8\nen_HK\nen_HK.utf8\nen_IE\nen_IE.utf8\nen_IE@euro\nen_IL\nen_IN\nen_NG\nen_NZ\nen_NZ.utf8\nen_PH\nen_PH.utf8\nen_SC.utf8\nen_SG\nen_SG.utf8\nen_US\nen_US.iso885915\nen_US.utf8\nen_ZA\nen_ZA.utf8\nen_ZM\nen_ZW\nen_ZW.utf8\n<\/pre>\n
Given that output I can only use the various “en_*” and the “C” collations but nothing else. Let’s try to use a collation from the list above:<\/p>\n
\npostgres=# create database tmpX with lc_collate='en_NZ.utf8';\nERROR:  new collation (en_NZ.utf8) is incompatible with the collation of the template database (en_US.UTF-8)\nHINT:  Use the same collation as in the template database, or use template0 as template.\n<\/pre>\n
That fails because there already could be some data in template1 which is not compatible with the locale specific in the create database command. As template0 is read only (more on the template databases here<\/a>) this one should be used when you want to create database using a locale which is different from template1:<\/p>\n
\npostgres=# create database tmpX with lc_collate='en_NZ.utf8' template=template0;\nCREATE DATABASE\npostgres=# create database tmpXX with lc_collate='C' template=template0;\nCREATE DATABASE\npostgres=# l\n                              List of databases\n   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges \n-----------+-------+----------+-------------+-------------+-------------------\n postgres  | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n template0 | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/pg            +\n           |       |          |             |             | pg=CTc\/pg\n template1 | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/pg            +\n           |       |          |             |             | pg=CTc\/pg\n tmp       | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n tmpx      | pg    | UTF8     | en_NZ.utf8  | en_US.UTF-8 | \n tmpxx     | pg    | UTF8     | C           | en_US.UTF-8 | \n(7 rows)\n<\/pre>\n
Basically we can use every locale that the operating provides but as there is nothing for German we can not use it. The question now is how we can install support for other locales on the operating system. For Red Hat 8 or CentOS 8 based systems this is quite easy as dnf<\/a> can be used to search for, and then install the language packs you need:<\/p>\n
\n[pg@centos8pg ~]$ dnf search locale | grep de\nLast metadata expiration check: 1:23:33 ago on Wed 04 Mar 2020 01:53:43 AM CET.\nperl-Encode-Locale.noarch : Determine the locale encoding\nperl-Locale-Codes.noarch : Distribution of modules to handle locale codes\nperl-Locale-US.noarch : Two letter codes for state identification in the United States and vice versa\nrubygem-locale.noarch : Pure ruby library which provides basic APIs for localization\nglibc-langpack-de.x86_64 : Locale data for de\nglibc-langpack-de.i686 : Locale data for de\nlangtable.noarch : Guessing reasonable defaults for locale, keyboard layout, territory, and language.\n<\/pre>\n
Once you’ve identified the correct package just install it:<\/p>\n
\n[pg@centos8pg ~]$ sudo dnf install -y glibc-langpack-de\n<\/pre>\n
If we ask the operating system supported for supported locales once more, the German ones now show up:<\/p>\n
\n[pg@centos8pg ~]$ localectl list-locales | grep de\nde_AT\nde_AT.utf8\nde_AT@euro\nde_BE\nde_BE.utf8\nde_BE@euro\nde_CH\nde_CH.utf8\nde_DE\nde_DE.utf8\nde_DE@euro\nde_IT\nde_IT.utf8\nde_LI.utf8\nde_LU\nde_LU.utf8\nde_LU@euro\n<\/pre>\n
This should give PostgreSQL all it needs to create the database with the German locale. Before we proceed with creating that database have a look at the pg_collation<\/a> catalog table:<\/p>\n
\npostgres=# select * from pg_collation ;\n  oid  |    collname     | collnamespace | collowner | collprovider | collisdeterministic | collencoding |   collcollate   |    collctype    | 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 12326 | ucs_basic       |            11 |        10 | c            | t                   |            6 | C               | C               | \n 12327 | C.utf8          |            11 |        10 | c            | t                   |            6 | C.utf8          | C.utf8          | \n 12328 | en_AG           |            11 |        10 | c            | t                   |            6 | en_AG           | en_AG           | \n 12329 | en_AU           |            11 |        10 | c            | t                   |            8 | en_AU           | en_AU           | \n 12330 | en_AU.utf8      |            11 |        10 | c            | t                   |            6 | en_AU.utf8      | en_AU.utf8      | \n 12331 | en_BW           |            11 |        10 | c            | t                   |            8 | en_BW           | en_BW           | \n 12332 | en_BW.utf8      |            11 |        10 | c            | t                   |            6 | en_BW.utf8      | en_BW.utf8      | \n 12333 | en_CA           |            11 |        10 | c            | t                   |            8 | en_CA           | en_CA           | \n 12334 | en_CA.utf8      |            11 |        10 | c            | t                   |            6 | en_CA.utf8      | en_CA.utf8      | \n 12335 | en_DK           |            11 |        10 | c            | t                   |            8 | en_DK           | en_DK           | \n 12336 | en_DK.utf8      |            11 |        10 | c            | t                   |            6 | en_DK.utf8      | en_DK.utf8      | \n 12337 | en_GB           |            11 |        10 | c            | t                   |            8 | en_GB           | en_GB           | \n 12338 | en_GB.iso885915 |            11 |        10 | c            | t                   |           16 | en_GB.iso885915 | en_GB.iso885915 | \n 12339 | en_GB.utf8      |            11 |        10 | c            | t                   |            6 | en_GB.utf8      | en_GB.utf8      | \n 12340 | en_HK           |            11 |        10 | c            | t                   |            8 | en_HK           | en_HK           | \n 12341 | en_HK.utf8      |            11 |        10 | c            | t                   |            6 | en_HK.utf8      | en_HK.utf8      | \n 12342 | en_IE           |            11 |        10 | c            | t                   |            8 | en_IE           | en_IE           | \n 12343 | en_IE@euro      |            11 |        10 | c            | t                   |           16 | en_IE@euro      | en_IE@euro      | \n 12344 | en_IE.utf8      |            11 |        10 | c            | t                   |            6 | en_IE.utf8      | en_IE.utf8      | \n 12345 | en_IL           |            11 |        10 | c            | t                   |            6 | en_IL           | en_IL           | \n 12346 | en_IN           |            11 |        10 | c            | t                   |            6 | en_IN           | en_IN           | \n 12347 | en_NG           |            11 |        10 | c            | t                   |            6 | en_NG           | en_NG           | \n 12348 | en_NZ           |            11 |        10 | c            | t                   |            8 | en_NZ           | en_NZ           | \n 12349 | en_NZ.utf8      |            11 |        10 | c            | t                   |            6 | en_NZ.utf8      | en_NZ.utf8      | \n 12350 | en_PH           |            11 |        10 | c            | t                   |            8 | en_PH           | en_PH           | \n 12351 | en_PH.utf8      |            11 |        10 | c            | t                   |            6 | en_PH.utf8      | en_PH.utf8      | \n 12352 | en_SC.utf8      |            11 |        10 | c            | t                   |            6 | en_SC.utf8      | en_SC.utf8      | \n 12353 | en_SG           |            11 |        10 | c            | t                   |            8 | en_SG           | en_SG           | \n 12354 | en_SG.utf8      |            11 |        10 | c            | t                   |            6 | en_SG.utf8      | en_SG.utf8      | \n 12355 | en_US           |            11 |        10 | c            | t                   |            8 | en_US           | en_US           | \n 12356 | en_US.iso885915 |            11 |        10 | c            | t                   |           16 | en_US.iso885915 | en_US.iso885915 | \n 12357 | en_US.utf8      |            11 |        10 | c            | t                   |            6 | en_US.utf8      | en_US.utf8      | \n 12358 | en_ZA           |            11 |        10 | c            | t                   |            8 | en_ZA           | en_ZA           | \n 12359 | en_ZA.utf8      |            11 |        10 | c            | t                   |            6 | en_ZA.utf8      | en_ZA.utf8      | \n 12360 | en_ZM           |            11 |        10 | c            | t                   |            6 | en_ZM           | en_ZM           | \n 12361 | en_ZW           |            11 |        10 | c            | t                   |            8 | en_ZW           | en_ZW           | \n 12362 | en_ZW.utf8      |            11 |        10 | c            | t                   |            6 | en_ZW.utf8      | en_ZW.utf8      | \n 12363 | en_AU           |            11 |        10 | c            | t                   |            6 | en_AU.utf8      | en_AU.utf8      | \n 12364 | en_BW           |            11 |        10 | c            | t                   |            6 | en_BW.utf8      | en_BW.utf8      | \n 12365 | en_CA           |            11 |        10 | c            | t                   |            6 | en_CA.utf8      | en_CA.utf8      | \n 12366 | en_DK           |            11 |        10 | c            | t                   |            6 | en_DK.utf8      | en_DK.utf8      | \n 12367 | en_GB           |            11 |        10 | c            | t                   |           16 | en_GB.iso885915 | en_GB.iso885915 | \n 12368 | en_GB           |            11 |        10 | c            | t                   |            6 | en_GB.utf8      | en_GB.utf8      | \n 12369 | en_HK           |            11 |        10 | c            | t                   |            6 | en_HK.utf8      | en_HK.utf8      | \n 12370 | en_IE           |            11 |        10 | c            | t                   |            6 | en_IE.utf8      | en_IE.utf8      | \n 12371 | en_NZ           |            11 |        10 | c            | t                   |            6 | en_NZ.utf8      | en_NZ.utf8      | \n 12372 | en_PH           |            11 |        10 | c            | t                   |            6 | en_PH.utf8      | en_PH.utf8      | \n 12373 | en_SC           |            11 |        10 | c            | t                   |            6 | en_SC.utf8      | en_SC.utf8      | \n 12374 | en_SG           |            11 |        10 | c            | t                   |            6 | en_SG.utf8      | en_SG.utf8      | \n 12375 | en_US           |            11 |        10 | c            | t                   |           16 | en_US.iso885915 | en_US.iso885915 | \n 12376 | en_US           |            11 |        10 | c            | t                   |            6 | en_US.utf8      | en_US.utf8      | \n 12377 | en_ZA           |            11 |        10 | c            | t                   |            6 | en_ZA.utf8      | en_ZA.utf8      | \n 12378 | en_ZW           |            11 |        10 | c            | t                   |            6 | en_ZW.utf8      | en_ZW.utf8      | \n(56 rows)\n<\/pre>\n
You will not see any of the collations you installed after the cluster was created. To actually make them visible in the catalog you need to manually add the collations from operating system:<\/p>\n
\npostgres=# select pg_import_system_collations('pg_catalog');\n pg_import_system_collations \n-----------------------------\n                          24\n(1 row)\n<\/pre>\n
You actually can create databases with new collations without importing them into the catalog table but we recommend to keep the catalog and the collations provided by the operating system in sync. Asking the catalog table once more the new “de_* collations will show up:<\/p>\n
\npostgres=# select * from pg_collation where collname ~ '^de_';\n  oid  |  collname  | collnamespace | collowner | collprovider | collisdeterministic | collencoding | collcollate | collctype  | collversion \n-------+------------+---------------+-----------+--------------+---------------------+--------------+-------------+------------+-------------\n 16386 | de_AT      |            11 |        10 | c            | t                   |            8 | de_AT       | de_AT      | \n 16387 | de_AT@euro |            11 |        10 | c            | t                   |           16 | de_AT@euro  | de_AT@euro | \n 16388 | de_AT.utf8 |            11 |        10 | c            | t                   |            6 | de_AT.utf8  | de_AT.utf8 | \n 16389 | de_BE      |            11 |        10 | c            | t                   |            8 | de_BE       | de_BE      | \n 16390 | de_BE@euro |            11 |        10 | c            | t                   |           16 | de_BE@euro  | de_BE@euro | \n 16391 | de_BE.utf8 |            11 |        10 | c            | t                   |            6 | de_BE.utf8  | de_BE.utf8 | \n 16392 | de_CH      |            11 |        10 | c            | t                   |            8 | de_CH       | de_CH      | \n 16393 | de_CH.utf8 |            11 |        10 | c            | t                   |            6 | de_CH.utf8  | de_CH.utf8 | \n 16394 | de_DE      |            11 |        10 | c            | t                   |            8 | de_DE       | de_DE      | \n 16395 | de_DE@euro |            11 |        10 | c            | t                   |           16 | de_DE@euro  | de_DE@euro | \n 16396 | de_DE.utf8 |            11 |        10 | c            | t                   |            6 | de_DE.utf8  | de_DE.utf8 | \n 16397 | de_IT      |            11 |        10 | c            | t                   |            8 | de_IT       | de_IT      | \n 16398 | de_IT.utf8 |            11 |        10 | c            | t                   |            6 | de_IT.utf8  | de_IT.utf8 | \n 16399 | de_LI.utf8 |            11 |        10 | c            | t                   |            6 | de_LI.utf8  | de_LI.utf8 | \n 16400 | de_LU      |            11 |        10 | c            | t                   |            8 | de_LU       | de_LU      | \n 16401 | de_LU@euro |            11 |        10 | c            | t                   |           16 | de_LU@euro  | de_LU@euro | \n 16402 | de_LU.utf8 |            11 |        10 | c            | t                   |            6 | de_LU.utf8  | de_LU.utf8 | \n 16403 | de_AT      |            11 |        10 | c            | t                   |            6 | de_AT.utf8  | de_AT.utf8 | \n 16404 | de_BE      |            11 |        10 | c            | t                   |            6 | de_BE.utf8  | de_BE.utf8 | \n 16405 | de_CH      |            11 |        10 | c            | t                   |            6 | de_CH.utf8  | de_CH.utf8 | \n 16406 | de_DE      |            11 |        10 | c            | t                   |            6 | de_DE.utf8  | de_DE.utf8 | \n 16407 | de_IT      |            11 |        10 | c            | t                   |            6 | de_IT.utf8  | de_IT.utf8 | \n 16408 | de_LI      |            11 |        10 | c            | t                   |            6 | de_LI.utf8  | de_LI.utf8 | \n 16409 | de_LU      |            11 |        10 | c            | t                   |            6 | de_LU.utf8  | de_LU.utf8 | \n(24 rows)\n<\/pre>\n
Creating the new database using one of “de_*” collations does work now:<\/p>\n
\npostgres=# create database tmp2 with LC_COLLATE = \"de_DE.UTF-8\" template=template0;\nCREATE DATABASE\npostgres=# l\n                              List of databases\n   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges \n-----------+-------+----------+-------------+-------------+-------------------\n postgres  | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n template0 | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/pg            +\n           |       |          |             |             | pg=CTc\/pg\n template1 | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/pg            +\n           |       |          |             |             | pg=CTc\/pg\n tmp       | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n tmp2      | pg    | UTF8     | de_DE.UTF-8 | en_US.UTF-8 | \n tmpx      | pg    | UTF8     | en_NZ.utf8  | en_US.UTF-8 | \n tmpxx     | pg    | UTF8     | C           | en_US.UTF-8 | \n<\/pre>\n
With PostgreSQL you can also specify the collation on a column level, so by now we can have a column using an “en” location and another one using a “de” collation:<\/p>\n
\npostgres=# create table t1 ( a text COLLATE \"de_DE.utf8\"\npostgres(#                 , b text COLLATE \"en_US.utf8\"\npostgres(#                 );\nCREATE TABLE\npostgres=# d t1\n                Table \"public.t1\"\n Column | Type | Collation  | Nullable | Default \n--------+------+------------+----------+---------\n a      | text | de_DE.utf8 |          | \n b      | text | en_US.utf8 |          | \n<\/pre>\n
If you want to use another collation which is currently not provided by the operating system the same rules apply as above:<\/p>\n
\npostgres=# create table t1 ( a text COLLATE \"de_DE.utf8\"\npostgres(#                 , b text COLLATE \"en_US.utf8\"\npostgres(#                 , c text COLLATE \"fr_FR.utf8\"\npostgres(#                 );\nERROR:  collation \"fr_FR.utf8\" for encoding \"UTF8\" does not exist\nLINE 3:                 , c text COLLATE \"fr_FR.utf8\"\n<\/pre>\n
You need to install support for that before you can use it. The same applies for queries, of course:<\/p>\n
\npostgres=# SELECT a < b COLLATE "de_DE" FROM t1;\n ?column? \n----------\n(0 rows)\n\npostgres=# SELECT a < b COLLATE "fr_FR" FROM t1;\nERROR:  collation "fr_FR" for encoding "UTF8" does not exist\nLINE 1: SELECT a < b COLLATE "fr_FR" FROM t1;\n<\/pre>\n
That’s it for the basics. More to come in another post.<\/p>\n","protected":false},"excerpt":{"rendered":"
When you work with databases sooner or later you will need to get in touch witch collations. It might be that you never needed to think about it as initdb picked a value that just worked for you and nobody in your database ever tried to use a collation that is not available in your […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[1855,77],"type_dbi":[],"class_list":["post-13606","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-collations","tag-postgresql"],"acf":[],"yoast_head":"\nCollations in PostgreSQL - 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\/collations-in-postgresql-basics\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Collations in PostgreSQL - basics\" \/>\n<meta property=\"og:description\" content=\"When you work with databases sooner or later you will need to get in touch witch collations. It might be that you never needed to think about it as initdb picked a value that just worked for you and nobody in your database ever tried to use a collation that is not available in your […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-03-05T15:44:31+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=\"12 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\/collations-in-postgresql-basics\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Collations in PostgreSQL – basics\",\"datePublished\":\"2020-03-05T15:44:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/\"},\"wordCount\":686,\"commentCount\":0,\"keywords\":[\"collations\",\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/\",\"name\":\"Collations in PostgreSQL - basics - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2020-03-05T15:44:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Collations in PostgreSQL – 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":"Collations in PostgreSQL - 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\/collations-in-postgresql-basics\/","og_locale":"en_US","og_type":"article","og_title":"Collations in PostgreSQL - basics","og_description":"When you work with databases sooner or later you will need to get in touch witch collations. It might be that you never needed to think about it as initdb picked a value that just worked for you and nobody in your database ever tried to use a collation that is not available in your […]","og_url":"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/","og_site_name":"dbi Blog","article_published_time":"2020-03-05T15:44:31+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Collations in PostgreSQL – basics","datePublished":"2020-03-05T15:44:31+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/"},"wordCount":686,"commentCount":0,"keywords":["collations","PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/","url":"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/","name":"Collations in PostgreSQL - basics - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-03-05T15:44:31+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/collations-in-postgresql-basics\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Collations in PostgreSQL – 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\/13606","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=13606"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13606\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=13606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=13606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=13606"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=13606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}