PostgreSQL Collation – part 1

PostgreSQL is (IMHO) much better than MySQL but unfortunately it  still lacks one of the greatest MySQL features: encoding.

MySQL Collation

Locale settings in MySQL are great. You can have different collation set for each database, table and even column (the same as it is in Interbase/Firebird). This gives the opportunity to simplify database structure. For example (I know, thery simplified example) when you have even a simple blog database and want to create a multilingual blog then it is enought co create one table for posts with two columns for body, each with different collation.

PostgreSQL Collation

In PostgreSQL situation is much different. So far you define collation only during `initdb` – that’s during cluste initialization (the cluster is the whole PostgreSQL instantion where you keep all your databases). Because of that you can’t have two columns with different collation. You even can’t have two databases with different language settings. For having such databases you need to have two different PostgreSQL instations: different installations or two processes run with pg_ctl -D “and here two different directories”.

This is not a problem to have two exactly different databases but this a real pain when you need (and I needed that too) two columns with quite different collation.

PostgreSQL Changes

In the future version of PostgreSQL the collation problem looks like be fixed quite nice, from 8.4 version there will be the possibility of creating two databases in one cluster with different collations. Unfortunately that won’t fix the problem with setting collation per column.

What is Collation

Collation is responsible for the alphabetical order and working functions like upper/lower for converting characters to upper and lower. If there is wrong collation then you will have problems with correct sorting texts in database.

Bookmark It

Related posts:

1. PostgreSQL Collation – 8.4 In the last PostgreSQL version (8.4) there is a small...
2. PostgreSQL Collation – part 2 Due to many questions about the PostgreSQL collation that were...
3. Another misleading “PostgreSQL vs MySQL review”!!! Yea… I wanted to write something as soon as I...
4. PostgreSQL Wishlist PostgreSQL is a great database, in my opinion much...
5. How to Allow For Max 4 Rows in Update (PostgreSQL) The Problem Question found on the net: how to restrict...
6. SQLAlchemy Defines New SQL Standard Dreaming About SQL Well, I thought that I know the...