我如何知道我的 PostgreSQL 服务器是否使用“C”语言环境？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1665726/
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
How do I know if my PostgreSQL server is using the "C" locale?
提问by Matt Huggins
I'm trying to optimize my PostgreSQL 8.3 DB tables to the best of my ability, and I'm unsure if I need to use varchar_pattern_opsfor certain columns where I'm performing a LIKEagainst the first N characters of a string. According to this documentation, the use of xxx_pattern_opsis only necessary "...when the server does not use the standard 'C' locale".
我正在尝试尽我所能优化我的 PostgreSQL 8.3 数据库表，但我不确定是否需要varchar_pattern_ops用于某些列，在这些列中我LIKE对字符串的前 N 个字符执行 a 。根据此文档，xxx_pattern_ops仅需要使用“...当服务器不使用标准的‘C’语言环境时”。
Can someone explain what this means? How do I check what locale my database is using?
有人可以解释一下这是什么意思吗？如何检查我的数据库使用的语言环境？
回答by Vinko Vrsalovic
Currently some locale [docs] support can only be set at initdb time, but I think the one relevant to _pattern_opscan be modified via SETat runtime, LC_COLLATE. To see the set values you can use the SHOWcommand.
目前，某些语言环境 [ docs] 支持只能在 initdb 时设置，但我认为_pattern_ops可以在运行时通过SETLC_COLLATE修改相关的语言环境。要查看设置值，您可以使用SHOW命令。
For example:
例如：
SHOW LC_COLLATE
_pattern_opsindexes are useful in columns that use pattern matching constructs, like LIKEor regexps. You still have to make a regular index (without _pattern_ops) to do equality search on an index. So you have to take all this into consideration to see if you need such indexes on your tables.
_pattern_ops索引在使用模式匹配结构（如LIKE或 regexp）的列中很有用。您仍然必须创建一个常规索引（没有_pattern_ops）才能对索引进行相等搜索。因此，您必须将所有这些都考虑在内，以查看您的表是否需要此类索引。
About what localeis, it's a set of rules about character ordering, formatting and similar things that vary from language/country to another language/country. For instance, the locale fr_CA (French in Canada) might have some different sorting rules (or way of displaying numbers and so on) than en_CA (English in Canada.). The standard "C" locale is the POSIX standards-compliant default locale. Only strict ASCII characters are valid, and the rules of ordering and formatting are mostly those of en_US (US English)
关于什么是语言环境，它是一组关于字符排序、格式和类似事物的规则，这些规则因语言/国家/地区而异。例如，语言环境 fr_CA（加拿大的法语）可能与 en_CA（加拿大的英语）有一些不同的排序规则（或显示数字的方式等）。标准的“C”语言环境是符合 POSIX 标准的默认语言环境。只有严格的 ASCII 字符有效，排序和格式规则大多是 en_US（美国英语）的规则
In computing, locale is a set of parameters that defines the user's language, country and any special variant preferences that the user wants to see in their user interface. Usually a locale identifier consists of at least a language identifier and a region identifier.
在计算中，区域设置是一组参数，用于定义用户的语言、国家/地区以及用户希望在其用户界面中看到的任何特殊变体首选项。通常一个区域标识符至少由一个语言标识符和一个区域标识符组成。
回答by Pavel Korshikov
psql -l
psql -l
according to handbook
根据手册
example output:
示例输出：
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+--------+----------+-------------+-------------+-------------------
packrd | packrd | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | packrd | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | packrd | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/packrd +
| | | | | packrd=CTc/packrd
template1 | packrd | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/packrd +
| | | | | packrd=CTc/packrd
(5 rows)
回答by rogerdpack
OK, from my perusings, it appears that this initial setting
好的，从我的阅读来看，这个初始设置似乎
initdb --locale=xxx
initdb --locale=xxx
--locale=locale
Specifies the locale to be used in this database. This is equivalent to specifying both --lc-collate and --lc-ctype.
basically specifies the "default" locale for all database that you create after that (i.e. it specifies the settings for template1, which is the default template). You can create new databases with a different locale like this:
基本上为您之后创建的所有数据库指定“默认”语言环境（即它指定模板 1 的设置，它是默认模板）。您可以使用不同的语言环境创建新数据库，如下所示：
Locale is different than encoding, you can manually specifyit and/or encoding:
区域设置与编码不同，您可以手动指定它和/或编码：
CREATE DATABASE korean WITH ENCODING 'EUC_KR' LC_COLLATE='ko_KR.euckr' LC_CTYPE='ko_KR.euckr' TEMPLATE=template0;
If you want to manually call it out.
如果你想手动调用它。
Basically if you don't specify it, it uses the system default, which is almost never "C".
基本上，如果您不指定它，它将使用系统默认值，几乎从不使用“C”。
So if your show LC_COLLATEreturns anything other than "C" or "POSIX" then you are not using the standard C localeand you will need to specify the xxx_pattern_ops for your indexes. Note also the caveatthat if you want to use the <, <=, >, or >= operators you need to create a second index without the xxx_pattern_ops flag (unless you are using the standard C locale on your database, which is rare...). For just == and LIKE(etc.) then you don't need a second index. If you don't need LIKEthen you don't need the index with xxx_pattern_ops, possibly, as well.
因此，如果您show LC_COLLATE返回“C”或“POSIX”以外的任何内容，那么您没有使用standard C locale并且您需要为您的索引指定 xxx_pattern_ops 。另请注意，如果您想使用 <、<=、> 或 >= 运算符，您需要创建没有 xxx_pattern_ops 标志的第二个索引（除非您在数据库上使用标准 C 语言环境，这种情况很少见。 ...）。对于 == 和LIKE（等）那么你不需要第二个索引。如果您不需要，LIKE那么您可能也不需要带有 xxx_pattern_ops 的索引。
Even if your indexes are defined to collate with the "default" like
即使您的索引被定义为与“默认”类似
CREATE INDEX my_index_name
ON table_name
USING btree
(identifier COLLATE pg_catalog."default");
This is not enough, unless the default is the "C" (or POSIX, same thing) collation, it can't be used for patterns like LIKE 'ABC%'. You need something like this:
这还不够，除非默认值是“C”（或 POSIX，同样的东西）排序规则，否则它不能用于LIKE 'ABC%'. 你需要这样的东西：
CREATE INDEX my_index_name
ON table_name
USING btree
(identifier COLLATE pg_catalog."default" varchar_pattern_ops);
回答by pawos
There is also another way (assuming you want to check them, not modify them):
还有另一种方法（假设您要检查它们，而不是修改它们）：
Check file /var/lib/postgres/data/postgresql.conf Following lines should be found:
检查文件 /var/lib/postgres/data/postgresql.conf 应找到以下几行：
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8' # locale for system error message strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
回答by Stephen Denne
If you've got the option...
如果你有选择...
You could recreate the database cluster with the C locale.
您可以使用 C 语言环境重新创建数据库集群。
You need to pass the locale to initdbwhen initializingyour Postgres instance.
初始化Postgres 实例时，您需要将语言环境传递给initdb。
You can do this regardless of what the server's default or user's locale is.
无论服务器的默认设置或用户的区域设置是什么，您都可以执行此操作。
That's a server administration command though, not a database schema designers task. The cluster contains all the databases on the server, not just the one you're optimising.
不过，这是一个服务器管理命令，而不是数据库模式设计者的任务。集群包含服务器上的所有数据库，而不仅仅是您正在优化的数据库。
It creates a brand new cluster, and does not migrate any of your existing databases or data. That'd be additional work.
它会创建一个全新的集群，并且不会迁移您现有的任何数据库或数据。那将是额外的工作。
Furthermore, if you're in a position where you can consider creating a new cluster as an option, you really should be considering using PostgreSQL 8.4 instead, which can have per-database locales, specified in the CREATE DATABASE statement.
此外，如果您处于可以考虑创建新集群作为选项的位置，您真的应该考虑使用 PostgreSQL 8.4，它可以具有在CREATE DATABASE 语句中指定的每个数据库的语言环境。
