Thread: Allow disabling folding of unquoted identifiers to lowercase

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"

I suspect this would be painful for the parser, unless you also enforced that all SQL keywords were in a specific case (all lower would be the minimal impact to the code).   otherwise the parser would have to lower() every token to check to see if its a keyword, but if not, revert it to its original case.

-- 
john r pierce, recycling bits in santa cruz

I suspect this would be painful for the parser, unless you also enforced that all SQL keywords were in a specific case (all lower would be the minimal impact to the code).   otherwise the parser would have to lower() every token to check to see if its a keyword, but if not, revert it to its original case.

​ Why? PostgreSQL is written in C. So use strncasecmp() instead of strncmp() or strcasecmp() instead of strcmp() to test for a token.

-- 
john r pierce, recycling bits in santa cruz

John McKown <john.archie.mckown@gmail.com> writes:
> ​Oh well, it's been interesting, but I don't think that we'll come to a
> resolution for the OP on this issue. I just blame both PostgreSQL and MySQL
> for this problem because the SQL standard says that the names are
> automatically UPPERCASEd unless enclosed in quotes. Not lower cased as
> PostgreSQL does it, nor unchanged as MySQL does it.​

Yeah, this isn't going to happen.  Years ago we looked into what it would
take to be able to flip a switch and have the standard-compliant behavior
(fold to upper not lower).  It was impractical then and no doubt is far
more so now.  I do not remember all the details, but there were multiple
pain points even in terms of server-side implementation, never mind all
the applications we'd break.

What the OP is asking for doesn't even have the argument "but it's
standards compliant!" going for it.  So I doubt we'd accept such a patch
even if someone managed to create one.

            regards, tom lane

Le 3 mai 2016 7:01 PM, "Evgeny Morozov" <evgeny.morozov@shift-technology.com> a écrit :
>
> That's an interesting idea! The client users would use is probably pgAdmin. I don't know whether pgAdmin parses the query, though. If it does then it should be relatively easy to add this. If not, I'd imagine it's not going to happen.
>

The pgAdmin query tool doesn't parse the query. It sends to the server without parsing itself.

> On 2 May 2016 at 13:59, John McKown <john.archie.mckown@gmail.com> wrote:
>>
>> On Mon, May 2, 2016 at 3:03 AM, Evgeny Morozov <evgeny.morozov+list+pgsql@shift-technology.com> wrote:
>>>
>>> On 30 April 2016 at 01:31, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>>
>>>> Yeah, this isn't going to happen.  Years ago we looked into what it would
>>>> take to be able to flip a switch and have the standard-compliant behavior
>>>> (fold to upper not lower).  It was impractical then and no doubt is far
>>>> more so now.  I do not remember all the details, but there were multiple
>>>> pain points even in terms of server-side implementation, never mind all
>>>> the applications we'd break.
>>>
>>>
>>> Alright, thanks to everyone for looking into this. Not knowing the code, I naively assumed it should be easy to add an option to not do the case folding.
>>>  
>>>>
>>>>
>>>> What the OP is asking for doesn't even have the argument "but it's
>>>> standards compliant!" going for it. 
>>>>
>>> Indeed, my argument was it would allow people to choose their own naming convention to (easily) use with Postgres, which would in turn make it easier to migrate from other RDBMSes to Postgres. Although, if you want a standard compliance argument, that can easily be added. :) Just have 3 options: fold to lowercase (current behaviour, default), fold to uppercase (standards compliant), do not fold (most flexible, compatible with MSSQL and MySQL).
>>>
>>> > So I doubt we'd accept such a patch even if someone managed to create one.
>>>
>>> Well, I was going to ask if paying someone to fix this was an option, but this preempts that!
>>
>>
>> ​I have a silly idea. IIRC, your original problem is that your users are in the habit of entering something like:
>>
>> select ... from SomeTable ...
>>
>> And MySQL would actually use the name "SomeTable" (case preserving) and not "sometable" (PostgreSQL) or "SOMETABLE" (SQL standard). What program are the users actually using to do the select? If it is something like "psql", perhaps it would actually be easier to create a modified version which automatically inserts the " marks for them. Of course, you are now doing double parsing of the SQL. First in the client, to modify it before sending to the server. Then again in the server. OK, maybe it is going too far. I guess this might be a "quote everything which is not a keyword" option for a psql replacement. Or whatever the front end is that the users use.
>>
>>
>> --
>> The unfacts, did we have them, are too imprecisely few to warrant our certitude.
>>
>> Maranatha! <><
>> John McKown
>
>

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/

> -----Ursprüngliche Nachricht-----
>
> 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.

I use 50% of my time Sybase and 50% PostgreSQL.

For me is the way Sybase works is just more convenient:

CREATE MyTable (MyColumn varchar);

creates a camel cased table MyType and field MyColumn.

SELECT * FROM SYSCATALOG gives MyTable.

This is better readable when you use long table / fiel names.

The user can then use whatever he wants: mytable, MyTable, myTABLE, ...

Klaus

On 08/05/2016 10:41, Klaus P. Pieper wrote:
> For me is the way Sybase works is just more convenient:
>
> CREATE MyTable (MyColumn varchar);
>
> creates a camel cased table MyType and field MyColumn.
>
> SELECT * FROM SYSCATALOG gives MyTable.
>
> This is better readable when you use long table / fiel names.

I reckon this is just a matter of style... I use underscores to make
long names readable...

   create table my_table_with_a_really_long_name;

....and it works fine.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie