Postgresql – How to resolve Postgresql error, ‘no collation was derived for column “foo” with collatable type citext’

citextcollationpostgresqlpostgresql-9.1

Just recently I upgraded from Postgresql 8.4.11 to 9.1.3. I used pg_dump in the process. Now I'm getting an error:

ERROR: no collation was derived for column "vin" with collatable type citext
HINT: Use the COLLATE clause to set the collation explicitly.

This is a continuation of the problem that I had earlier. It seems the fix suggested in the Release Notes did not catch custom domains. It seems as if the CREATE DOMAIN statement in 8.4 didn't even support the COLLATE clause.

This is how I created the vin type,

CREATE DOMAIN inventory.valid_vin AS citext
  CHECK ( inventory.valid_vin( VALUE ) );

Best Answer

For more information on this find our correspondence on this thread:

Thread: http://archives.postgresql.org/pgsql-general/2012-05/msg00262.php

Problem

The problem comes right from the horses mouth, Tom Lane,

Well, if that's how you're creating citext *, then yeah it's broken. As of 9.1 the citext type needs to be created with the attribute "COLLATABLE = true". The suggested UPDATE statements are a means of correcting a failure to do that after-the-fact, but they don't cover any domains that have already been created on top of citext.

Source: http://archives.postgresql.org/pgsql-general/2012-05/msg00262.php

* I was creating citext however my specific dump had it created, there was nothing special.

So a dump/restore from 8.3 -> 9.1 is not supported if you've got a domain over citext.

Solution

You can get around this problem by first doing:

CREATE EXTENSION citext before you load the dump.
or, by hacking the patch to operate on DOMAINS over citext.

Related Solutions

Postgresql – How to resolve Postgresql error, “could not determine which collation to use for string comparison”

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

Postgresql – WorkAround for PHP PDO(with libpq V 9.1.4) binding for use of CITEXT

Locate the namespace inside which the citext type resides:

select nspname from pg_type t join pg_namespace n
  on n.oid=t.typnamespace where typname='citext';

Prepend that namespace (normally, 'public', but it might be different in your case and it might explain the problem) to the cast to citext:

$sql = "SELECT column1 from schema1.column1 where column1 = 'value'::public.citext";

If that solves the problem but in a way you find inelegant, you might reconsider how you're using schemas and search_path: make sure that all your custom types are accessible no matter what.