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 5 years old! Most likely, its content is outdated. Especially if it's technical.

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:

Follow @peterbe on Twitter


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

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 Keyword:
How do log ALL PostgreSQL SQL happening 20 July 2015
Fastest database for Tornado 09 October 2013
Connecting with psycopg2 without a username and password 24 February 2011
Optimization of getting random rows out of a PostgreSQL in Django 23 February 2011
Fastest "boolean SQL queries" possible with Django 14 January 2011
Related by Text:
Migration of Postgres 9.2 to 9.3 with Homebrew and json_enhancements 30 April 2014
"No space left on device" on OSX Docker 03 October 2017
Correction: running Django tests with MongoDB is NOT slow 30 May 2010
hastebinit - quickly paste snippets into 11 October 2012
Public Class Fields saves sooo many keystrokes in React code 14 April 2017