Why does a comparison between CITEXT and TEXT fail?

Question

As expected, when doing an equality test like this, the comparison succeeds:

CREATE TABLE citext_test (
    value citext PRIMARY KEY
);

INSERT INTO citext_test VALUES ('one');
INSERT INTO citext_test VALUES ('two');

SELECT * FROM citext_test WHERE value = 'One';

However, if the value being compared against is of type 'text', the comparison fails:

SELECT * FROM citext_test WHERE value = 'One'::text;

Using EXPLAIN, it appears the raw value in the first example is cast to CITEXT:

Index Only Scan using citext_test_pkey on citext_test  (cost=0.15..8.17 rows=1 width=32)
  Index Cond: (value = 'One'::citext)

I'd like to compare against TEXT columns from joined tables. Do I really need to cast these columns to CITEXT to get comparisons to work? I thought part of the advantage of using CITEXT was not having to remember to add things like this (e.g. LOWER(some_value));

score 6 · Accepted Answer · answered Jul 27 '17 at 14:02

Do I really need to cast these columns to CITEXT to get comparisons to work?

Yes, that's what you have to do. That's not how the operator works. You're supposed to set this type on the table so you do not have to this. If one says it's case insensitive text, and the other says it's case-sensitive text, the case-sensitive text wins. The reason why the first example works is because

SELECT * FROM citext_test WHERE value = 'One';

is essentially the same as

SELECT * FROM citext_test WHERE value = 'One'::unknown;

Internally, that gets promoted by the operator to citext. You can disambiguate with the cast,

SELECT * FROM citext_test WHERE value = 'One'::citext;

or the functional form,

SELECT * FROM citext_test WHERE citext_eq(value, 'One');

But the apropos solution would be to ALTER TABLE for the table you're joining to and set its type to citext too.

score 2 · Answer 2 · answered Jul 27 '17 at 15:57

What happens under the hood to execute the first comparison corresponds to what is described in Operator type resolution:

https://www.postgresql.org/docs/current/static/typeconv-oper.html

2. Check for an operator accepting exactly the input argument types. If one exists (there can be only one exact match in the set of operators considered), use it.

a. If one argument of a binary operator invocation is of the unknown type, then assume it is the same type as the other argument for this check. Invocations involving two unknown inputs, or a unary operator with an unknown input, will never find a match at this step.

For the 2nd comparison, it's evaluated to false because some of the rules explained in 3. Look for the best match lead apparently to a cast to text. But you may convince PostgreSQL to evaluate text=citext the way you want by adding a function/operator couple with an exact type match:

CREATE FUNCTION my_citext_eq_text(citext,text) returns boolean as
  'select $1=cast($2 as citext)' language sql immutable;

CREATE OPERATOR = (procedure=my_citext_eq_text,
   leftarg=citext, rightarg=text);

I think that extensions don't do this by default in general to avoid having the number of operators growing quadratically with the number of types.

See the citext--<latest-version-number>.sql installed in your extension directory for the operators and implicit casts defined with citext.

Why does a comparison between CITEXT and TEXT fail?

2 Answers2

Linked