CODEWITHSUNDEEP
postgresql
Code
Code

Reputation: 6251

Cross-platform collation and ctype

I code on a Windows machine but use a Linux machine in production.

The PostgreSQL lc_collate and lc_ctype are OS-dependent, which presents a few problems.

When I create a database (e.g. create database db with encoding 'UTF8' lc_collate 'en_US.UTF-8' lc_ctype 'en_US.UTF-8'), I have to detect the OS and specify the appropriate values, which is troublesome.

The biggest problem is when copying the production database to the development machine using pg_dump and pg_restore. There is no easy way to replace these values when dumping/restoring.

What is the best way to workaround these problems?

Upvotes: 3

Views: 340

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246513

Yes, this is annoying.

PostgreSQL has recognized that using the OS collations was maybe not the best idea, so they introduced ICU collations recently which are operating system independent.

Unfortunately, these cannot be used with CREATE DATABASE yet.

Collations cause little trouble when using pg_dump/pg_restore.
All you have to do is create the database at the target system using an existing collation and then import the dump into that database.

Upvotes: 4

Related Questions