Re: case_preservation_and_insensitivity = on

On 2/19/17 4:51 PM, Joel Jacobson wrote:
>> But once you've already
>> decided to have a hard-and-fast rule that the names must be unique
>> after lower-casing, there's no obvious benefit to rejecting queries
>> that mention the same name with different case.
> Exactly, that trade-off is necessary, otherwise such queries would be ambiguous.
>
> I think a good general philosophy for the PostgreSQL project would be to
> try to look at how to meed the needs for new users of new projects
> in a way that don't impair things for existing users,
> by accepting the new users might have to live with some trade-offs
> for their new feature to be possible to implement,
> such as in this case that the trade-off is to not be able to create
> objects of different casing with the same lowercase names,
> a tradeoff that I personally think would not be a problem for most projects,
> since it seems unlikely you would both have a "users" table and a
> "Users" table in the same database.

There's a serious problem with that, though: there certainly *could* be
existing users that depend on the difference between "Users" and users,
and there's no way we can just leave them out in the cold.

Even if the project decided that "Users" and users is stupid and that we
should deprecate it, I think the odds of also deciding to tell existing
users to re-write their apps are zero.

So no matter how this is designed, there has to be some way for existing
users to be able to continue relying on "Users" and users being
different. AFAICT that rules out any chance of this being a GUC, because
you can't take a GUC into consideration when creating a unique index.

What would work is an initdb option that controls this: when ignoring
case for uniqueness is disabled, your new column would simply be left as
NULL. With some extra effort you could probably allow changing that on a
running database as well, just not with something as easy to change as a
GUC.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)