BUG #6259: Collation Error with Citext fields - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      6259
Logged by:          Stan S
Email address:      ssantiago@adinfocenter.com
PostgreSQL version: 9.1.1
Operating system:   CentOS release 5.4 (Final)
Description:        Collation Error with Citext fields
Details:

Greetings, I'm encountering collation related errors specifically with
CITEXT fields. Environment details are below.

I've also captured for test scenarios showing how using varchar() instead of
citext works fine which leads me to believe this is not an environment
specific issue.

Environment:
-bash-3.2$ /usr/pgsql-9.1/bin/psql -d shard_1 -U postgres
psql (9.1.1)
Type "help" for help.

shard_1=# \set
AUTOCOMMIT = 'on'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
VERBOSITY = 'default'
VERSION = 'PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit'
DBNAME = 'shard_1'
USER = 'postgres'
PORT = '5432'
ENCODING = 'SQL_ASCII'



TEST 1: Using citext
----------------------------

shard_1=# CREATE TABLE users (nickname CITEXT PRIMARY KEY,pass TEXT   NOT
NULL);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey"
for table "users"


shard_1=# INSERT INTO users VALUES ( 'larry',  'aaa' );
INSERT 0 1

shard_1=# INSERT INTO users VALUES ( 'Tom',   'bbb' );
ERROR:  could not determine which collation to use for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.

- Updating implicit index users_pkey is failing.

TEST 2: Using varchar
-------------------------------

shard_1=# CREATE TABLE users_2 (nickname varchar(100) PRIMARY KEY,pass TEXT
 NOT NULL);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "usersq_pkey"
for table "users_2"


shard_1=#  INSERT INTO users_2 VALUES ( 'larry',  'aaa' );
INSERT 0 1
shard_1=#  INSERT INTO users_2 VALUES ( 'larry2',  'bbb' );
INSERT 0 1

shard_1=# select * from usersq order by nickname;
 nickname |               pass
----------+----------------------------------
 larry    | 2845555df4713dbd143c2ebdbf03fb0b
 larry2   | cf21f7aed35c58054c8ea7622d7f7b23



TEST 3: Using citext with collate keyword -- FAILS
----------------------------------------------------------------------

shard_1=# CREATE TABLE users (nickname CITEXT COLLATE "C" PRIMARY KEY,pass
TEXT   NOT NULL);
ERROR:  collations are not supported by type citext


TEST 4: Using varchar(7) with collate keyword -- SUCCESS
----------------------------------------------------------------------------
-----
shard_1=# CREATE TABLE users (nickname varchar(7) COLLATE "C" PRIMARY
KEY,pass TEXT   NOT NULL);

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey"
for table "users"