[HACKERS] Case sensitivity in identifiers - Mailing list pgsql-hackers

Kurt J. Lidl writes ("Re: Re[2]: [HACKERS] Upper and lower case column names "):
>
> >On Tue, 18 Mar 1997 lsh@lubrizol.com wrote:
> >> >Why is it desirable to do this?  Work has to be done to throw out case
> >> >and information is lost with no advantage in db space or server
> >> >speed.  What is the advantage for caselessness?  Does anyone use a
> >> >single-case terminal anymore?
> >>     I wrote a message asking exactly the same thing yesterday.
> >>     Somehow I don't think it got posted, or maybe I missed it.
> >>     I would also like for someone to explain why this was done
> >>     on the first place.  I am sure there are a lot of people out
> >>     there who use Case-sensitive column names and would find it
> >>     almost impossible to migrate from 6.0 to 6.1.
> >
> >The main reason is that since most commercial databases use
> >non-case-sensitive identifiers, many programs that connect to Postgres
> >through layers like ODBC or JDBC will assume that Postgres' identifiers
> >are non-case-sensitive, too.  Assuming we want compatibility, it's a
> >lot easier to change Postgres than to get companies like Microsoft,
> >Borland, etc. to change their programs.
>
> Yes, it will be a lot easier to make Postgresql be ansi complient
> than it will be to convince companies to make their products be
> non-ansi complient.
>
> There are a great many things that suck about ansi SQL, but being
> gratuitously different isn't one of the things that should be pursued.

I'd agree with the principle of case insensitivity, particularly in
the interests of ANSI compatibility.

Having just fetched and built 6.1, however, I am appalled at the way
it has been done. Perhaps it had to be that way, but off the top of my
head, I can't see why. I use cases as a matter of style, not syntax,
and 6.1 has thrown away all my cases. Wouldn't it be possible to keep
the identifiers as defined, but ignore the case at run-time, so to
speak? All I want to do is this:

=> create table FOO (ID int4, Field1 char16, FieldTwo varchar(80));
=> \d FOO
Table    = FOO
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| ID                               | int4                             |     4 |
| Field1                           | char16                           |    16 |
| FieldTwo                         | varchar                          |    80 |
+----------------------------------+----------------------------------+-------+

or somesuch.

Right now saying '\d FOO' says 'Couldn't find table FOO!' so somebody
missed a bit of case-sensitivity code somewhere, but I'm sure you see
the point: *store* the identifiers as given, but allow access to them
with insensitivity.

Such a change would also mean that third-party apps which read the
identifiers would not break (at least for this reason). In particular
I use PHP/FI, now I know that there's a 'fix' but (i) I was happy with
my tried, tested and tuned httpd, and (ii) there must be other cases
like this; it's not as if PHP/FI was abusing the Postgres95 API
particularly.

While ANSI SQL may be case insensitive, does it insist that everything
is forced to lower case? Does it break compliance to implement what I
have suggested? Isn't this the elegant way to do it? If you'd done it
this way, how many people would have noticed?

Do I hear howling from the implementors here?

Sorry if this has been covered before on this list. I'm about 4000
messages behind right now ;-(

John.                                                         _     _
- --                                                        _  | |___| |_  _ _
John Robinson      46 Bank Street, Dumfries DG1 2PA, UK  | |_| / . \ ' \| ' \
+44 1387 247249     http://www.intelligent.co.uk/~john/   \___/\___/_||_|_||_|
Aye Oedipus, yer a complex f*cker right enough                  --Irvine Welsh

------------------------------