PostgreSQL 比较字符串设置

Question

我有一个查询，它在不同的服务器上返回不同的结果。

select ' s' > '.'

第一台服务器返回真，第二台服务器返回假。哪些设置会导致此问题？

Answer 1

您的数据库有不同的 COLLATION :

The collation feature allows specifying the sort order and character classification behavior of data per-column, or even per-operation. This alleviates the restriction that the LC_COLLATE and LC_CTYPE settings of a database cannot be changed after its creation.

select name, setting
from pg_settings
where name in ('lc_collate', 'server_encoding', 'client_encoding');

-- explicit collation
select ' s' COLLATE "POSIX" > '.' COLLATE "POSIX"

SQLFiddle Demo