How do I resolve Postgresql error, "could not determine which collation to use for string comparison"? 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.
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);