Postgres collation errors on CITEXT fields when upgrading to 9.1
21 May 2012
Just in case this hits you too when you use
CITEXT fields that were originally defined in a Postgres before version 9.1.
ProgrammingError: could not determine which collation to use for string comparison HINT: Use the COLLATE clause to set the collation explicitly.
This can happen if you use something like:
name is a case insensitive text field.
After some googling around and shooting in the dark I found the the only way to crack this is to run this command:
CREATE EXTENSION citext FROM unpackaged;
Hope that helps some poor schmuck with the same problem.
If you have problems applying this to new tables in Postgres 9.1 you might need to run this instead:
CREATE EXTENSION citext WITH SCHEMA public ;