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.


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 Keyword:
How I performance test PostgreSQL locally on macOS 10 December 2018
Best EXPLAIN ANALYZE benchmark script 19 April 2018
When Docker is too slow, use your host 11 January 2018
How do log ALL PostgreSQL SQL happening 20 July 2015
Fastest database for Tornado 09 October 2013