Postgres collation errors on CITEXT fields when upgrading to 9.1

21 May 2012   1 comment   Web development

Powered by Fusion×

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.
Thank you for posting a comment

Your email will never ever be published


Related posts

Previous:
Secs sell! How I cache my entire pages (server-side) 10 May 2012
Next:
How I deal with deferred image loading in Javascript 08 June 2012
Related by keywords:
Connecting with psycopg2 without a username and password 24 February 2011
Adding a year in PostgreSQL 04 February 2004
Integer division in programming languages 04 August 2004
Fastest database for Tornado 09 October 2013
pg_class to check if table exists 20 April 2005
Optimization of getting random rows out of a PostgreSQL in Django 23 February 2011
To sub-select or not sub-select in PostgreSQL 31 August 2009
Date formatting in python or in PostgreSQL 20 July 2004
UPPER vs. ILIKE 19 April 2010
Speed test between django_mongokit and postgresql_psycopg2 09 March 2010
PostgreSQL, MySQL or SQLite 04 April 2004
Fastest "boolean SQL queries" possible with Django 14 January 2011