主页 / dba / 问题 / 17604
Accepted
Evan Carroll
Asked: 2012-05-09 13:03:45 +0800 CST

如何解决 Postgresql 错误“无法确定用于字符串比较的排序规则”？

  • 772

当我运行这个 SQL 脚本时，我得到：

错误：无法确定用于字符串比较的排序规则

这是第 34 行的内容：

CREATE TEMPORARY TABLE weight_options AS
  SELECT
    io.lot_id
    , io.vin
    , map.fkey_style AS chrome_styleid
    , SUM( CASE WHEN co.code = io.code THEN 1 ELSE 0 END ) AS exact_option
    , SUM( CASE WHEN length(io.code) = 2 AND co.code LIKE io.code || '%' THEN 1 ELSE 0 END ) AS appx_option
    , (
      SUM( CASE WHEN co.code = io.code THEN 2 ELSE 0 END )
      + SUM( CASE WHEN length(io.code) = 2 AND co.code LIKE io.code || '%' THEN 1 ELSE 0 END )
    ) AS option_weight
  FROM inventory.options AS io
  -- options *must* have same index as inventory.vehicles
  JOIN chrome_vinmatch_best_match AS vd
    USING ( vin )
  JOIN chrome_vinmatch.style AS map
    ON ( vd.chrome_patternid = map.fkey_pattern )
  JOIN chrome_nvd.options AS co
    ON ( io.code = co.code AND map.fkey_style = co.fkey_style AND io.code IS NOT NULL )
  GROUP BY io.lot_id, io.vin, map.fkey_style
;

我刚刚从安装迁移8.4.11到安装9.1.3使用pg_dump.

postgresql postgresql-9.1

1 个回答

  1. Best Answer

    在9.1.2 的发行说明中发现错误

    使 contrib/citext 的升级脚本修复 citext 列和索引的排序规则 (Tom Lane)

    在 9.1 之前的服务器进行 pg_upgrade 期间，现有的 citext 列和索引未正确标记为可整理数据类型。这会导致对它们的操作失败，并出现诸如“无法确定用于字符串比较的排序规则”之类的错误。此更改允许它们通过在 CREATE EXTENSION citext FROM unpackaged 期间将 citext 模块升级为适当的 9.1 扩展的相同脚本来修复。

    如果您有一个以前升级过的数据库遇到此问题，并且您已经运行了 CREATE EXTENSION 命令，您可以手动运行（以超级用户身份）在 SHAREDIR/extension/citext--unpackaged-- 末尾找到的 UPDATE 命令1.0.sql。（如果您不确定 SHAREDIR 在哪里，请运行 pg_config --sharedir。）

    所以我在那个文件中查找了UPDATE命令并找到了这些：

    UPDATE pg_catalog.pg_type SET typcollation = 100
WHERE oid = 'citext'::pg_catalog.regtype;

UPDATE pg_catalog.pg_attribute SET attcollation = 100
WHERE atttypid = 'citext'::pg_catalog.regtype;

UPDATE pg_catalog.pg_index SET indcollation[0] = 100
WHERE indclass[0] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[1] = 100
WHERE indclass[1] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[2] = 100
WHERE indclass[2] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[3] = 100
WHERE indclass[3] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[4] = 100
WHERE indclass[4] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[5] = 100
WHERE indclass[5] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[6] = 100
WHERE indclass[6] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[7] = 100
WHERE indclass[7] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);
    • 4

相关问题