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.


If you have problems applying this to new tables in Postgres 9.1 you might need to run this instead:



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

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