How do I resolve Postgresql error, "could not determine which collation to use for string comparison"?

Question

When ever I run this SQL script I get:

ERROR: could not determine which collation to use for string comparison

This is what is on line 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
;

I just did a migration from an install of 8.4.11 to an install of 9.1.3 using pg_dump.

Evan Carroll · Accepted Answer · 2012-05-08T21:37:05.487

Found the error in the Release Notes for 9.1.2

Make contrib/citext's upgrade script fix collations of citext columns and indexes (Tom Lane)

Existing citext columns and indexes aren't correctly marked as being of a collatable data type during pg_upgrade from a pre-9.1 server. That leads to operations on them failing with errors such as "could not determine which collation to use for string comparison". This change allows them to be fixed by the same script that upgrades the citext module into a proper 9.1 extension during CREATE EXTENSION citext FROM unpackaged.

If you have a previously-upgraded database that is suffering from this problem, and you already ran the CREATE EXTENSION command, you can manually run (as superuser) the UPDATE commands found at the end of SHAREDIR/extension/citext--unpackaged--1.0.sql. (Run pg_config --sharedir if you're uncertain where SHAREDIR is.)

So I looked in that file for the UPDATE commands and found these:

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);

How do I resolve Postgresql error, "could not determine which collation to use for string comparison"?

1 Answers1

Linked