如何在不同的语言环境中使用PostgreSQL upper()函数? [英] How to use PostgreSQL upper() function with a different locale?

问题描述

我在共享主机上有一个PostgreSQL数据库，由于区域设置，在本地数据库中使用upper函数的结果有所不同.

I have a PostgreSQL database on a shared host and the result of using the upper function is different in my local database because of the locale setting.

这就是我想要的，并且在我的本地环境中拥有:

Here is what I want, and have in my local environment:

SELECT version();
-- "PostgreSQL 8.4.16 on i386-apple-darwin10.8.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5666) (dot 3), 64-bit"

SHOW LC_COLLATE;
-- "fr_FR.UTF-8"

SELECT upper('étienne');
-- "ÉTIENNE"

这是我在生产环境中拥有的东西:

Here is what I have in the production environment:

SELECT version();
-- "PostgreSQL 9.0.13 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2, 64-bit"

SHOW LC_COLLATE;
-- "C"

SELECT upper('étienne');
-- éTIENNE

现在，由于生产环境位于共享主机中，因此由于主机策略，我无法更改语言环境.那么，在使用上层函数时还有另一种方法来获得预期的结果吗?

Now, because the production environment is in a shared host, I cannot change the locale due to the host policies. So, is there another way to have the expected result when using upper function?

推荐答案

请注意，您不能覆盖版本8.4或9.0中的排序规则(如其他答案所示). Postgres 9.1 引入了此功能.

Note that you cannot override the collation (like demonstrated by other answers) in your versions 8.4 or 9.0. This feature was introduced with Postgres 9.1.

在早期版本中，您无法更改创建数据库时选择的排序规则. COLLATE关键字不存在，如果尝试使用SET LC_COLLATE = ...，则会出现错误:

In earlier versions, you cannot change the collation chosen when the database was created. The COLLATE key word does not exist and you get an error if you try SET LC_COLLATE = ...:

ERROR: parameter "lc_collate" cannot be changed

您需要更新的版本.

-> SQLfiddle

对不起，但是你很不幸.

Sorry, but you are out of luck.

这篇关于如何在不同的语言环境中使用PostgreSQL upper()函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！