Re: Allow disabling folding of unquoted identifiers to lowercase - Mailing list pgsql-general

From Peter J. Holzer
Subject Re: Allow disabling folding of unquoted identifiers to lowercase
Date
Msg-id 20160508092653.GB27578@rorschach.hjp.at
Whole thread Raw
In response to Allow disabling folding of unquoted identifiers to lowercase  (Evgeny Morozov <evgeny.morozov+list+pgsql@shift-technology.com>)
Responses Re: Allow disabling folding of unquoted identifiers to lowercase
List pgsql-general
On 2016-04-29 19:21:30 +0200, Evgeny Morozov wrote:
> It would be great if Postgres had a server setting that allowed the automatic
> folding of identifiers to lowercase to be disabled, so that camel case
> identifiers could be used without having to quote every single identifier, i.e.
>
> SELECT MyColumn FROM MyTable ORDER BY MyColumn
>
> instead of
>
> SELECT "MyColumn" FROM "MyTable" ORDER BY "MyColumn"
[...]
> My company is looking into doing this. Currently our table and column names
> exactly match our class and property names, which are in camel case. MSSQL
> supports this just fine. To move to Postgres we would have to either quote
> *everything* or translate names back-and-forth between code and database. Both
> options are OK for auto-generated SQL, but we also have many users writing
> ad-hoc SQL queries. Having to quote everything would have those users screaming
> to switch back to MSSQL very quickly! That leaves us with the mapping approach,
> which is doable, but also a constant "mental speedbump" at best.

What exactly is the problem you are trying to solve?

If you and your users are consistent about never using quotes, your
users can write:

SELECT MyColumn FROM MyTable ORDER BY MyColumn;

It will select mycolumn from mytable, but that doesn't matter, since you
created the table with

    CREATE MyTable (MyColumn varchar);

so you really have a table mytable with a column mycolumn, not a table
MyTable with a column MyColumn.

There are three potential problems I can see:

1) Users might be confused that PgAdmin (or whatever tool they use to
   inspect the database) displays all the names in lowercase, and they
   might find a name like sometablewithareallylongname less readable
   than SomeTableWithAReallyLongName.

2) Since case doesn't matter, they might be inconsistent: One programmer
   might write MyTable, another mytable, the third MYTABLE, ...

3) You might want to use a tool to automatically generate SQL queries,
   but that tool quotes identifiers.

        hp

--
   _  | Peter J. Holzer    | I want to forget all about both belts and
|_|_) |                    | suspenders; instead, I want to buy pants
| |   | hjp@hjp.at         | that actually fit.
__/   | http://www.hjp.at/ |   -- http://noncombatant.org/

Attachment

pgsql-general by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Ubuntu/Debian PGDP
Next
From: "Klaus P. Pieper"
Date:
Subject: Re: Allow disabling folding of unquoted identifiers to lowercase