citext: citext=text is case sensitive - Mailing list pgsql-general

Hi all

While looking into a Stack Overflow question, I noticed that the
comparision of citext = text is case sensitive.

While I'm sure that's by design, it isn't obvious in the documentation,
and it was a little surprising to me. It's particularly confusing when
combined with prepared statements from drivers like PgJDBC that bind
parameters as `text', as it makes it a real PITA to use 'citext'
usefully. That's particularly the case when use is via query generators
or ORMs like the SO question, which boils down to:

     PREPARE some_query(text) AS SELECT * FROM some_table WHERE
citext_column = $1;
     EXECUTE('compared_case_sensitively');

In case anyone wants to override the default citext=text operation,
which works by doing an implicit cast of `citext` to `text` and using
the `text=text` operator, the following appears to work, but I'm unsure
if it's introducing unforseen problems or complexities. Is there any way
to avoid repeating everything for the two argument orders?

CREATE FUNCTION citext_eq(citext,text) RETURNS boolean AS $$
SELECT citext_eq($1,$2::citext);
$$ LANGUAGE 'sql' IMMUTABLE;

CREATE FUNCTION citext_ne(citext,text) RETURNS boolean AS $$
SELECT citext_ne($1,$2::citext);
$$ LANGUAGE 'sql' IMMUTABLE;

CREATE FUNCTION citext_eq(text,citext) RETURNS boolean AS $$
SELECT citext_eq($1::citext,$2);
$$ LANGUAGE 'sql' IMMUTABLE;

CREATE FUNCTION citext_ne(text,citext) RETURNS boolean AS $$
SELECT citext_ne($1::citext,$2);
$$ LANGUAGE 'sql' IMMUTABLE;

CREATE OPERATOR = (
     LEFTARG    = CITEXT,
     RIGHTARG   = TEXT,
     COMMUTATOR = =,
     NEGATOR    = <>,
     PROCEDURE  = citext_eq,
     RESTRICT   = eqsel,
     JOIN       = eqjoinsel,
     HASHES,
     MERGES
);

CREATE OPERATOR <> (
     LEFTARG    = CITEXT,
     RIGHTARG   = TEXT,
     NEGATOR    = =,
     COMMUTATOR = <>,
     PROCEDURE  = citext_ne,
     RESTRICT   = neqsel,
     JOIN       = neqjoinsel
);

CREATE OPERATOR = (
     LEFTARG    = TEXT,
     RIGHTARG   = CITEXT,
     COMMUTATOR = =,
     NEGATOR    = <>,
     PROCEDURE  = citext_eq,
     RESTRICT   = eqsel,
     JOIN       = eqjoinsel,
     HASHES,
     MERGES
);

CREATE OPERATOR <> (
     LEFTARG    = TEXT,
     RIGHTARG   = CITEXT,
     NEGATOR    = =,
     COMMUTATOR = <>,
     PROCEDURE  = citext_ne,
     RESTRICT   = neqsel,
     JOIN       = neqjoinsel
);

It's possible to extend the above for the other operators by following
the same pattern. See the contents of the citext--1.0.sql extension
script for definitions.

--
Craig Ringer