如何修改PostgreSQL数据库的Encoding

软件开发 发表时间:2016-01-16 更新时间:2016-01-16

参考:
http://stackoverflow.com/questions/5090858/how-do-you-change-the-character-encoding-of-a-postgres-database

1. 最安全的方法: 备份, 再还原

 

1. Dump your database
2. Drop your database,
3. Create new database with the different encoding
4. Reload your data.

Make sure the client encoding is set correctly during all this.

Source: http://archives.postgresql.org/pgsql-novice/2006-03/msg00210.php

2.另外一个方法: 直接修改编码:

如果数据库中的字符本来就是按照期望的编码方式编码的, 那么可以直接修改编码为期望的编码.
用npgsql插入的中文字符都是utf8的, 因此如果数据库的原编码是 SQL_ASCII , 那么可以安全的直接修改为 UTF8 

update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'thedb'

First off, Daniel's answer is the correct, safe option.

For the specific case of changing from SQL_ASCII to something else, you can cheat and simply poke the pg_database catalogue to reassign the database encoding. This assumes you've already stored any non-ASCII characters in the expected encoding (or that you simply haven't used any non-ASCII characters).

Then you can do:

update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'thedb'

This will not change the collation of the database, just how the encoded bytes are converted into characters (so now length('£123') will return 4 instead of 5). If the database uses 'C' collation, there should be no change to ordering for ASCII strings. You'll likely need to rebuild any indices containing non-ASCII characters though.

Caveat emptor. Dumping and reloading provides a way to check your database content is actually in the encoding you expect, and this doesn't. And if it turns out you did have some wrongly-encoded data in the database, rescuing is going to be difficult. So if you possibly can, dump and reinitialise.