On Jul 16, 2008, at 11:20, Robert Treat wrote:
>> I was thinking about this a bit last night and wanted to fill things
>> out a bit.
>>
>> As a programmer, I find Donald Fraser's hindsight to be more
>> appealing, because at least that way I have the option to do matching
>> against CITEXT strings case-sensitively when I want to.
>>
>> OTOH, if what we want is to have CITEXT work more like a case-
>> insensitive collation, then the expectation from the matching
>> operators and functions might be different. Does anyone have any idea
>> whether regex and LIKE matching against a string in a case-
>> insensitive
>> collation would match case-insensitively or not? If so, then maybe
>> the
>> regex and LIKE ops and funcs *should* match case-insensitively? If
>> not, or if only for some collations, then I would think not.
>>
>> Either way, I know of no way, currently, to allow functions like
>> replace(), split_part(), strpos(), and translate() to match case-
>> insensitiely, even if we wanted to. Anyone have any ideas?
>>
>>> * If the answer is "no", how can I make those functions behave case-
>>> insensitively? (See the "TODO" tests.)
>>>
>>> * Should there be any other casts? To and from name, perhaps?
>>
>
> AIUI, your propsing the following:
>
> select 'x'::citext = 'X'::citext;
> ?column?
> ----------
> t
> (1 row)
>
> select 'x'::citext ~ 'X'::citext;
> ?column?
> ----------
> f
> (1 row)
>
> I understand the desire for flexibility, but the above seems wierd
> to me.
That's what Donald Fraser suggested, and I see some value in that, but
wanted to get some other opinions. And you're right, that does seem a
bit weird.
The trouble is that, right now:
template1=# select regexp_replace( 'fxx'::citext, 'X'::citext, 'o'); regexp_replace
---------------- fxx
(1 row)
So there's an inconsistency there. I don't know how to make that work
case-insensitively.
Best,
David
