Evan Carroll <me@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
