如何为 PostgreSQL 设置千位分隔符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3360752/
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 to set thousand separator for PostgreSQL?
提问by Micha? Niklas
I want to format long numbers using thousand separator. It can be done using to_charfunction just like:
我想使用千位分隔符格式化长数字。它可以使用to_char函数来完成，就像：
SELECT TO_CHAR(76543210.98, '999G999G990D00')
But when my PostgreSQL server with UTF-8 encoding is on Polish version of Windows such SELECT ends with:
但是当我使用 UTF-8 编码的 PostgreSQL 服务器在波兰版本的 Windows 上时，这样的 SELECT 以：
ERROR: invalid byte sequence for encoding "UTF8": 0xa0
HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
In to_charpattern Gis described as: group separator (uses locale).
This SELECT works without error when server is running on Linux with Polish locale.
在to_char模式中G被描述为：组分隔符（使用语言环境）。当服务器在具有波兰语语言环境的 Linux 上运行时，此 SELECT 可以正常工作。
As a workaround I use space instead of Gin format string, but I think there should be way to set thousand separator just like in Oracle:
作为一种解决方法，我使用空格而不是G格式字符串，但我认为应该有办法设置千位分隔符，就像在 Oracle 中一样：
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=', ';
Is such setting available for PostgreSQL?
这种设置可用于 PostgreSQL 吗？
回答by Ali Akbar
If you use psql, you can execute this:
如果使用psql，则可以执行以下操作：
\pset numericlocale
Example:
例子：
test=# create temporary table a (a numeric(20,10));
CREATE TABLE
test=# insert into a select random() * 1000000 from generate_series(1,3);
INSERT 0 3
test=# select * from a;
a
-------------------
287421.6944910590
140297.9311533270
887215.3805568810
(3 rows)
test=# \pset numericlocale
Showing locale-adjusted numeric output.
test=# select * from a;
a
--------------------
287.421,6944910590
140.297,9311533270
887.215,3805568810
(3 rows)
回答by Mike Sherrill 'Cat Recall'
I'm pretty sure the error message is literally true: 0xa0 isn't a valid UTF-8 character.
我很确定错误消息确实是真的：0xa0 不是有效的 UTF-8 字符。
My home server is running PostgreSQL on Windows XP, SP3. I can do this in psql.
我的家庭服务器在 Windows XP SP3 上运行 PostgreSQL。我可以在 psql 中做到这一点。
sandbox=# show client_encoding;
client_encoding
-----------------
UTF8
(1 row)
sandbox=# show lc_numeric;
lc_numeric
---------------
polish_poland
(1 row)
sandbox=# SELECT TO_CHAR(76543210.98, '999G999G990D00');
to_char
-----------------
76 543 210,98
(1 row)
I don't get an error message, but I get garbage for the separator. Could this be a code page issue?
我没有收到错误消息，但我收到了分隔符的垃圾信息。这可能是代码页问题吗？
As a workaround I use space instead of G in format string
作为一种解决方法，我在格式字符串中使用空格而不是 G
Let's think about this. If you use a space, then on a web page the value might split at the end of a line or at the boundary of a table cell. I'd think a nonbreaking space might be a better choice.
让我们考虑一下。如果您使用空格，则在网页上，该值可能会在行尾或表格单元格的边界处拆分。我认为不间断的空间可能是更好的选择。
And, in Unicode, a nonbreaking space is 0xa0. In Unicode, not in UTF8. (That is, 0xa0 can't be the first byte of a UTF8 character. See UTF-8 Bit Distribution.)
而且，在 Unicode 中，不间断空格是 0xa0。在 Unicode 中，而不是在 UTF8 中。（也就是说， 0xa0 不能是 UTF8 字符的第一个字节。请参阅UTF-8 位分布。）
Another possibility is that your client is expecting one byte order, and the server is giving it a different byte order. Since the numbers are single-byte characters, the byte order wouldn't matter until, well, it mattered. If the client is expecting a big endian MB character, and it got a little endian MB character beginning with 0xa0, I'd expect it to die with the error message you saw. I'm not sure I have a way to test this before I go to work today.
另一种可能性是您的客户端期望一个字节顺序，而服务器给它一个不同的字节顺序。由于数字是单字节字符，因此字节顺序无关紧要，直到很重要。如果客户端需要一个大端 MB 字符，并且它有一个以 0xa0 开头的小端 MB 字符，我希望它会随着您看到的错误消息而消失。在我今天上班之前，我不确定我有没有办法对此进行测试。
