Cross-platform collation and ctype

user_4593869 0
Viewed 170

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?

postgresql
1 Answers

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.

user_64643080
Related