在Heroku postgres上设置语言环境 [英] Set locale on Heroku postgres

问题描述

我在Heroku使用基本数据库计划。它运行在支持语言环境的Postgres 9.1上。我在应用程序中进行排序时遇到了问题，因为ÅÄÖ的字符处理不当（就像他们在瑞典应该那样）。

要设置的设置是 LC_COLLATE ，它处理字符串排序。问题是我找不到在Heroku上设置它的任何方法。创建的数据库得到 lc_collat​​e = en_US.UTF-8 ，但我需要将它设置为 sv_SE.UTF-8 。

这个 LC_COLLATE 设置在创建数据库时不能改变，因此我可以' t

所以，我该如何设置它？

解决方案

你是正确的，不能改变数据库的默认排序规则; LC_COLLATE 是在Heroku数据库服务器上设置的一个环境变量，它在您的控制之外，并且在数据库创建之前已经设置。但是，您可以为各个列设置默认排序规则：

  CREATE TABLE new_table（
 foo varchar COLLATEsv_SE .UTF-8，
 bar varchar COLLATEsv_SE.UTF-8
）; 
 
 ALTER TABLE existing_table ALTER COLUMN baz TYPE varchar COLLATEsv_SE.UTF-8; 
  

更多信息，请参阅 22.2。整理支持在PostgreSQL手册中。

您可能需要也可能不需要先到 CREATE COLLATION 。此外，所有这一切都取决于Heroku数据库服务器安装了适当的语言环境数据 - 尽管如果他们不这样做，您可能会很好地请求部署，因为它不会伤害任何人。

如果不这样做，你当然可以在EC2中运行你自己的PostgreSQL实例，无论你想要什么样的自定义设置。这需要投入管理时间，但老实说，运行9.1非常简单，甚至包括流式复制。甚至可能会更便宜。缺点是保持数据库运行成为你的问题，而不是Heroku操作团队的问题。

I'm using the Basic database plan at Heroku. This runs on Postgres 9.1 which has support for locales. I'm having issues with sorting in my app because the characters ÅÄÖ is not treated properly (as they should in Sweden).

The setting to set is LC_COLLATE, which handles string ordering. The problem is that I can't find any way to set this on Heroku. The databases that are created get lc_collate=en_US.UTF-8, but I need to set it to sv_SE.UTF-8.

This LC_COLLATE setting can't be changed when the database has been created, hence I can't change it through the psql console.

So, how can I set this?

解决方案

You're correct that can't change the database's default collation; LC_COLLATE is an environment variable set on the Heroku database servers, which is both outside your control and already set before your database was created. You can, however, set the default collation for individual columns:

CREATE TABLE new_table (
    foo varchar COLLATE "sv_SE.UTF-8",
    bar varchar COLLATE "sv_SE.UTF-8"
);

ALTER TABLE existing_table ALTER COLUMN baz TYPE varchar COLLATE "sv_SE.UTF-8";

For more, see 22.2. Collation Support in the PostgreSQL manual.

You may or may not need to CREATE COLLATION first. Additionally, all this depends on the Heroku database servers having the proper locale data installed -- although if they don't, you could probably ask nicely to get that deployed, since it wouldn't hurt anyone.

Failing that, you could of course run your own PostgreSQL instances in EC2, with whatever custom setup you want. That would require investing administration time, but honestly running 9.1 is pretty straightforward, even including streaming replication. Might even be cheaper too. The downside there is that keeping the database running becomes your problem instead of a problem for the Heroku ops team.

这篇关于在Heroku postgres上设置语言环境的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！