How do I resolve Postgresql error, 'no collation was derived for column "foo" with collatable type citext'?
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.