On 3/18/19 5:18 AM, Morris de Oryx wrote:
> Sounds like I may have touched a nerve with some. If so, no offense
> intended!
>
> There are cases where case-sensitivity is required or desirable, it
> would be silly to argue otherwise. Where you have such cases, then
> case-sensitive queries are great. Some RDBMS systems default to
> case-sensitive searches, others default to case-blind searches. I'll
> take that to mean that either choice has merit, and it's not foolish
> to prefer either. I need case-blind searches vanishingly close to 100%
> of the time, but other people have different conditions and may find
> that they almost never need case-blind searches. I find this extreme
> hard to imagine, but find it easy to imagine people who need
> case-sensitive searches quite often.
>
> What I've been thinking about most are user-driven searches on
> user-oriented data. Users, at least any user I've ever had, don't want
> case-sensitive searches. They also don't care about diacritical
> characters, at least in English. I worked for a French company for
> many years. Diacritical searches were not always preferred, but
> sometimes were. It depends on your user community and their
> requirements and norms. Sometimes it comes down to an individual user.
> Options are good! I was really just trying to warn someone coming from
> a base-blind default about Postgres behavior because, well, it hurts
> if you aren't expecting it. That doesn't make Postgres wrong (it's not
> that kind of a choice), but it is important to know about.
>
> I'm new to Postgres (only about a year in), and it's great. But I'm
> used to a case-blind search as a default. And, honestly, I can *never*
> remember a case when a user asked for a case-sensitive search. Ever.
> In 30+ years. Maybe it's just me. Just kidding, it's not just me. If
> you're presenting users with a search interface, you can find out by
> asking them. Or you can AB test a search UI where there is the option
> of case-sensitive/blind searching, but you randomly flip which is the
> default. For users, case-sensitive searches are assumed. That's what
> Google does. Seriously, Google === Search. It's not a hard test to
> run. If you find that with a case-blind search, 30% of user tick the
> box to make it case-sensitive, then you've got users that often do
> care about case-sensitive search.
>
> And since it seems to be unclear at a few places in the discussion
> above: *It absolutely makes sense to store data in its original form*
> and to allow for case-sensitive searches, when required. It would be
> very weird to store
>
> / Call me back Ishmael, I've gotta go.../
>
> and get back anything else, be that
>
> / call me back ishmael, i've gotta go...
> /
>
> or
>
> / CALL ME BACK ISHMAEL, I'VE GOTTA GO.../
>
> As far as I understand it in Postgres, you can:
>
> * Use something like UPPER or LOWER in Every. Single. Search.
>
> * Fold text to one case consistently in an index to make searches
> case-blind.
>
> * Use citext.
>
> * Teach and convince every user to enter and search for data
> case-sensitively correctly Every. Single. Time.
>
> On that last point, good luck. Here's an example, I'm keen on birds.
> Do you write it:
>
> Black-shouldered Kite
> Black-Shouldered Kite
>
>
Since you say you're new(ish) to postgres I have to ask have you bumped
into the tilde(~) operator for text? And the critical tildeAstersik ( ~* )?