citext: citext=text is case sensitive

[Craig Ringer <ringerc@xxxxxxxxxxxxx>]

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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general