Thread: BUG #6053: Can't do DISTINCT on citext column

The following bug has been logged online:

Bug reference:      6053
Logged by:          Regina
Email address:      lr@pcorp.us
PostgreSQL version: 9.1beta1
Operating system:   Windows
Description:        Can't do DISTINCT on citext column
Details:

I have a table I created like this:
-- With and without the COLLATE in there I get the same error.

 CREATE TABLE test
(
  fullname citext COLLATE pg_catalog."POSIX",
);

INSERT INTO  test(fullname) VALUES('test1'),('test2');

When I do a:
SELECT DISTINCT fullname FROM test;

or a

SELECT DISTINCT fullname COLLATE pg_catalog."POSIX" FROM test;


It gives error:

ERROR:  could not determine which collation to use for lower() function
HINT:  Use the COLLATE clause to set the collation explicitly.

On Sun, Jun 5, 2011 at 3:22 PM, Regina <lr@pcorp.us> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A06053
> Logged by: =A0 =A0 =A0 =A0 =A0Regina
> Email address: =A0 =A0 =A0lr@pcorp.us
> PostgreSQL version: 9.1beta1
> Operating system: =A0 Windows
> Description: =A0 =A0 =A0 =A0Can't do DISTINCT on citext column
> Details:
>
> I have a table I created like this:
> -- With and without the COLLATE in there I get the same error.
>
> =A0CREATE TABLE test
> (
> =A0fullname citext COLLATE pg_catalog."POSIX",
> );
>
> INSERT INTO =A0test(fullname) VALUES('test1'),('test2');
>
> When I do a:
> SELECT DISTINCT fullname FROM test;
>
> or a
>
> SELECT DISTINCT fullname COLLATE pg_catalog."POSIX" FROM test;
>
>
> It gives error:
>
> ERROR: =A0could not determine which collation to use for lower() function
> HINT: =A0Use the COLLATE clause to set the collation explicitly.

I don't see a reply to this bug report, but I believe Tom fixed it in beta2.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company