CODEWITHSUNDEEP
postgresqllocale
user1493834
user1493834

Reputation: 766

how to change the locale of an existing database

I have a PostgreSQL database. It has a table which stores menu items(labels).These menu items are stored in English. is there any way to convert these stored items (100+) to Japanese language by using localization feature? because my customer box is UNIX with locale set to Japanese.

Upvotes: 7

Views: 23940

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 325141

Reading between the lines, I'd say your database is in iso-8869-1 or WIN1252 encoding, which are 1-byte encodings for English languages.

If so, while you could transcode to a Japanese-specific encoding, they're mostly quite limited - both in their coverage of English (Roman) characters, and their coverage of Kanji/Hiragana. Japanese doesn't work wonderfully in a 1-byte encoding. Shift-JIS was an attempt to work around that, but it's an awful text encoding to work with and PostgreSQL will refuse to run with it.

Instead, convert the database to utf-8. This will support all your existing content and all new content. UTF-8 will work for any language.

To do so:

CREATE DATABASE mydb_new ENCODING 'UTF-8' 
    LC_COLLATE 'jp_JA.UTF-8' LC_CTYPE 'jp_JA.UTF-8';

then pg_dump the old database, and pg_restore to the new database. Afterwards you can rename the databases to swap them over.

All characters in latin-1 are valid in utf-8, so there won't be issues loading the dump.

You/your customer might need to generate/install the ja_JP.UTF-8 locale (if on Linux/BSD). How to do that is somewhat distro/platform specific.

Upvotes: 6

Related Questions