Re: Custom Domain; migration from 8.4 to 9.1 and COLLATE

Evan Carroll <me(at)evancarroll(dot)com> writes:
>> Could we see the complete context for this?
> Sure.
> dealermade=# CREATE OR REPLACE TEMP VIEW chrome_vinmatch_best_match AS
> dealermade-# SELECT DISTINCT ON (v.vin) v.vin, vd.*
> dealermade-# FROM inventory.view_in_stock_vehicles AS v
> dealermade-# JOIN chrome_vinmatch.view_vin_decode AS vd
> dealermade-# ON substring(v.vin FROM 0 FOR 9) =
> substring(vd.pattern FROM 0 FOR 9)
> dealermade-# AND v.vin LIKE vd.pattern
> dealermade-# ORDER BY vin, length(pattern) DESC
> dealermade-# ;
> ERROR: no collation was derived for column "vin" with collatable type citext
> HINT: Use the COLLATE clause to set the collation explicitly.

> v.vin is the column with the custom DOMAIN.

Hm, this example works fine for me in 9.1 branch tip, and I see no
relevant-looking patches in the commit logs since 9.1.3. What I suspect
is that you are being bit by the failure of 9.1.0 or 9.1.1 to set
pg_type.typcollation for the citext data type, as per this item in the
9.1.2 release notes:

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

regards, tom lane