Postgres collation errors on CITEXT fields when upgrading to 9.1

21 May 2012   1 comment   Web development

Mind that age!

This blog post is 8 years old! Most likely, its content is outdated. Especially if it's technical.

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:

WHERE name='peter'

when field 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.

UPDATE

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 ;

Comments

Dallas G.

A very sincere THANKS from a very "poor schmuck".

P.S. Maybe its implied (I'm still a very green DBA), but the only thing I would add is a note stating that you'll need to create the extension on every database which contains citext values.

Your email will never ever be published

Related posts