在 Postgresql 中更改数据库排序规则、Ctype
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19420925/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Change Database Collation, Ctype in Postgresql
提问by Mani Deep
how do I change Collation, cType to - en_INfrom en_US.UTF-8
如何将 Collation, cType 更改为 - en_INfromen_US.UTF-8
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
: postgres=CTc/postgres
my current postgresversion is 8.4 ive installed it using
我当前的 postgresversion 是 8.4 我使用安装它
sudo apt-get install postgresql-8.4 postgresql-contrib-8.4
im doing this in my ubuntu amazon server ec2
我在我的 ubuntu amazon 服务器 ec2 中这样做
采纳答案by Chris Travers
My recommendation:
我的建议：
take a pg_dumpall
re-initialize the db cluster, making sure the locale information is correct
restore your dump.
拿一个 pg_dumpall
重新初始化数据库集群，确保区域信息正确
恢复你的转储。
I have found that sometimes it is possible that one may have to create a db with template template0 (-T template0 from bash or WITH TEMPLATE template0from psql) to use a non-init-db locale.
我发现有时可能必须使用模板 template0（来自 bash 或WITH TEMPLATE template0psql 的-T template0）创建一个数据库来使用非 init-db 语言环境。
回答by Yaroslav Nikitenko
It's not necessary to recreate the whole database cluster. You need however to recreate your database.
没有必要重新创建整个数据库集群。但是，您需要重新创建数据库。
Run createdb with these options (man createdb):
使用以下选项 ( man createdb)运行 createdb ：
-E encoding, --encoding=encoding
Specifies the character encoding scheme to be used in this
database. The character sets supported by the PostgreSQL server
are described in Section 22.3.1, “Supported Character Sets”, in
the documentation.
-l locale, --locale=locale
Specifies the locale to be used in this database. This is
equivalent to specifying both --lc-collate and --lc-ctype.
--lc-collate=locale
Specifies the LC_COLLATE setting to be used in this database.
--lc-ctype=locale
Specifies the LC_CTYPE setting to be used in this database.
It seems you really can't change the collation of an existing database:
看来您真的无法更改现有数据库的排序规则：
=> ALTER DATABASE dbname SET "Collate" To Russian;
ERROR: unrecognized configuration parameter "Collate"
Note that you can set collation for a table or a column, see a good tutorialon collations in PostgreSQL.
请注意，您可以为表或列设置排序规则，请参阅有关 PostgreSQL 中排序规则的优秀教程。
回答by Chris
I had to change to POSIX.UTF-8. I managed that with the following commands:
我不得不更改为 POSIX.UTF-8。我使用以下命令进行了管理：
su postgres
psql
\l
update pg_database set datcollate='POSIX.UTF-8', datctype='POSIX.UTF-8' where datname='databasename';
\l
回答by zaman linux
its Very very Simple Solutions.
它非常非常简单的解决方案。
Step1. su - postgres
Step2. psql
Setp3. update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'icinga';(dont'forget to add ;)
Step4. \l to check
第1步。su - postgres
第2步。psql
Setp3。update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'icinga';（不要忘记添加；）
Step4。\l to check
