Re: caes insensitivity

On Fri, Apr 17, 2020 at 4:10 PM PG Doc comments form <noreply(at)postgresql(dot)org>
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/12/sql-syntax-lexical.html
> Description:
>
> The docs are misleading about the case insensitive nature, as someone new
> to
> postgresql, I found it very confusing.
>
> The doc (under 4.1.1. Identifiers and Key Words) says:
>
> UPDATE MY_TABLE SET A = 5; can equivalently be written as: uPDaTE my_TabLE
> SeT a = 5;
>
> However, this will never work unless the underlying table is created in
> lower case.

Which is was unless you specified the name of the table using a mixed-case
quoted identifier when you created it. In which case you cannot expect a
unquoted identifier to match it. That it will by happenstance should you
decide to quote the identifier and write it in all lowercase is a
side-effect and not something to rely upon. i.e., Either quote your
identifiers, or don't, both upon object creation and in queries.

> The "case insensitivity" appears to make everything in the query
> lowercase. This is not the same thing as being case insensitive.
>

Two unquoted identifiers match against each other regardless of the case
they are written in. They were not sensitive to case, i.e.,
case-insensitive.

so if I have a table created as My_Table,

Which requires double-quoting...

> I can *only* access it by quoting
>

correct

> it. 'Select * from my_table' does not work, nor does 'Select * from
> MY_TABLE', and nor does the expected 'Select * from My_Table'.
>

Nope, because generally speaking *identifier* and *"identifier"* are two
different names - though in this specific case, interchangeable.

> If the query was case insensitive, it should still work, finding the
> underlying table regardless of its case. But as this obviously does not
> work, it should not be documented as such.
>

How's this sound?

Key words are case insensitive; unquoted identifiers are folded to lower
case.

Its repetitive with what is below but seems worthy of repeating to clarify
if reworking things to be less redundant isn't appealing.

Instead the reality of the system should be documented - that all queries
> are turned into lower case and the lower-case transformation is run, unless
> the text is explicitly quoted.
>

It does, further down.

"""
Quoting an identifier also makes it case-sensitive, whereas unquoted names
are always folded to lower case.
"""

David J.