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 6 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.

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 Keyword:
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
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
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
When Docker is too slow, use your host 11 January 2018
UPPER vs. ILIKE 19 April 2010
Sequences in PostgreSQL and rolling back transactions 12 May 2009