3

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));

4

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.

1
  • 1
    You're right, "the case-sensitive text wins" is the problem. I tend to think it shouldn't, since the CITEXT doesn't care about case you can think of it as matching whatever the TEXT holds. Maybe it's just a side effect of CITEXT being an extension, or maybe someone's put more thought into it than myself and knows cases where this would lead to undesired results.
    – antsyawn
    Jul 27 '17 at 21:36
2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.