Hello hanckers,
We use this simple function to workaround citext=text behavior.
create extension citext;
CREATE FUNCTION citext_eq( citext, text )
RETURNS bool
AS 'citext'
LANGUAGE C IMMUTABLE STRICT;
CREATE OPERATOR = (
LEFTARG = CITEXT,
RIGHTARG = TEXT,
COMMUTATOR = =,
NEGATOR = <>,
PROCEDURE = citext_eq,
RESTRICT = eqsel,
JOIN = eqjoinsel,
HASHES,
MERGES
);
select 'xexe'::text = 'Xexe'::citext
select 'xexe' = 'Xexe'::citext
select 'xexe'::citext = 'Xexe'
select 'xexe'::citext = 'Xexe'::text
select 'xexe'::citext = 1234::text
CREATE or replace FUNCTION ttt(t text)
RETURNS bool
AS
$$select $1 = 'Ttt'::citext $$
LANGUAGE sql;
select ttt('ttt')
But in general, it is wrong to compare values with different types.
We used this and other strange cases like TO_CHAR for type text for our
own BI system with user defined calculations .
On Mon, May 7, 2018 at 12:09 PM, Shay Rojansky <[email protected]> wrote:
>
>>> Thanks for the input. It's worth noting that the equality operator
>>> currently works in the same way: citext = text comparison is (surprisingly
>>> for me) case-sensitive.
>>>
>>> My expectation was that since citext is supposed to be a
>>> case-insensitive *type*, all comparison operations involving it should be
>>> case-insensitive;
>>>
>>
>> Comparison requires both things to be the same type. The rules for
>> implicitly converting one type to another prefer the core type text over
>> the extension type citext.
>>
>> IOW, there is no such operator =(citext,text) and thus "citext = text
>> comparison" is technically invalid.
>>
>> At this point we're sorta stuck with our choice, and while individual
>> databases can implement their own functions and operators there is value in
>> doing things the way the system provides to minimize future confusion and
>> bugs.
>>
>
> OK, thanks for everyone's input.
>
>
--
--Regards, Sergey Mirvoda
