Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ) );
share|improve this question
    
Ouch! My first instinct would be to alter any such columns to the base citext type, drop the domain, create it again, and alter the columns back; but that's a totally untested suggestion which might not be on target. –  kgrittn May 8 '12 at 22:15
    
    
Thanks for cross-linking your SO post and list post. Much appreciated. –  Craig Ringer Oct 19 '12 at 0:08

1 Answer 1

up vote 2 down vote accepted

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

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.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.