PostgreSQL – Why Comparison Between CITEXT and TEXT Fails

citextpostgresql

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

Best Answer

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.

Related Question