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.
