主页 / dba / 问题 / 298651
Accepted
Héctor
Asked: 2021-08-28 01:49:34 +0800 CST

区分大小写不一致

  • 772

我正在尝试学习和理解排序规则以及 postgresql 如何比较和排序字符串，但我发现不协调，我不知道我错过了什么。

查询：

SELECT datname, datcollate FROM pg_database;

返回我的所有数据库都使用 en_GB.UTF-8 排序规则。

和查询：

SELECT table_schema, 
   table_name, 
   column_name,
   collation_name
FROM information_schema.columns
WHERE collation_name is not null
ORDER BY table_schema,
     table_name,
     ordinal_position;

返回所有列都有“C”排序规则，这意味着（根据我的研究）没有指定排序规则。所以，我假设使用了数据库排序规则，不是吗？

编辑：我的错误在这里。上一个查询不会为所有列返回结果，仅对那些collation_name不为空的列返回结果，具有空排序规则的列是继承数据库排序规则的列。所以我认为 foo.bar 列有C排序规则，但null实际上是。

好吧，当我运行查询时出现了不一致：

SELECT "name" FROM foo.bar ORDER BY "name" ASC;

其中“名称”列数据类型是文本，而 foo.bar 是用户创建的 schema.table 组合。结果它就像人类一样按字母顺序排列。从 a/A 到 Z/z，不管是大写还是小写。

但是，如果我运行以下查询：

SELECT "table_name"
FROM information_schema.tables
WHERE
    "table_name" ~ 'some_pattern'
    AND table_schema = 'foo'
    AND table_type = 'BASE TABLE'
ORDER BY "table_name" ASC;

它以类似机器的方式对结果进行排序，逐字节比较，因此在小写之前排序大写。

为什么会有这种差异？我已经看到“table_name”列数据类型不是“文本”而是“名称”，但我找不到它是否有事可做。

将数据库排序规则设置为 en_GB.UTF-8 不足以以人类的方式比较文本？

谢谢你的时间。

postgresql order-by

1 个回答

  1. Best Answer

    PostgreSQL 中的排序规则是这样确定的（简化，详细见文档）：

    • 如果存在显式COLLATE子句，则确定排序规则

    • 否则，如果使用某种排序规则定义表列，则使用该排序规则

    • 如果该列未使用排序规则定义（它具有“默认排序规则”），则使用数据库排序规则。

    现在该列bar.name未使用排序规则定义，因此en_GB.UTF-8使用。

    但是information_schema.columns有 type 的列information_schema.sql_identifier，它是数据类型的域name，它总是使用C排序规则。因此，在该查询中，大写字母排在小写字母之前。

    要改变这一点，您应该明确指定排序规则：

    ORDER BY table_schema COLLATE "en_GB.UTF-8",
         table_name COLLATE "en_GB.UTF-8",
         ordinal_position;
    • 5

相关问题