Thread: BUG #4047: case preserve for columns

BUG #4047: case preserve for columns

From

"Eugen Konkov"

Date:

21 March 2008, 14:55:12

The following bug has been logged online:

Bug reference:      4047
Logged by:          Eugen Konkov
Email address:      Eugen.Konkov@aldec.com
PostgreSQL version: 8.3
Operating system:   Windows XP
Description:        case preserve for columns
Details:

1)
SELECT Id FROM MYTABLE;
$sth= fetchall_hashref()
expected: $sth->[i]{Id}
actual: $sth->[i]{id}

2)
SELECT "Id" as ID FROM MYTABLE;
$sth= fetchall_hashref()
expected: $sth->[i]{ID}
actual: $sth->[i]{id}

How ask postgre preserve column names as I query them?
This is a VERY, VERI BIG issue if I need quote all those as:
a) SELECT "Id" FROM MYTABLE;
b) SELECT "Id" as "ID" FROM MYTABLE;
Because of I need by hand quote of 100K queries
But very difficult will be to deal with dinamically generated queries.

Father more "Id" mysql count as string and not field name. So ported to
PostgreSQL queries is not compatible back with MySQL =(

Is there any workaround other then quoting to get working 1) and 2) as
expected?

thanks

PS. other discussion:
http://www.issociate.de/board/post/10327/Case_insensitivity_in_column_and_ta
ble_names.html

Re: BUG #4047: case preserve for columns

From

John R Pierce

Date:

21 March 2008, 15:10:15

Eugen Konkov wrote:
> Is there any workaround other then quoting to get working 1) and 2) as
> expected?
>

The SQL standard actually says everything not quoted should be upshifted
to upper case.   PostgreSQL opted for lower case a long time ago and has
stuck with this for legacy.

If your "100K lines of SQL" are autogenerated, why not have the
autogenerator quote everything thats supposed to be case specific,
alleviating any such problems ?

Re: BUG #4047: case preserve for columns

From

Date:

21 March 2008, 16:06:20

It is have no any matter to me if it is upshifted or lowershifted on server
sidethe standard does not specify that output of queries MUST be
lowershifted/upshifted.why you do this?----- Original Message -----
From: "John R Pierce" <pierce@hogranch.com>
To: "Eugen Konkov" <Eugen.Konkov@aldec.com>
Cc: <pgsql-bugs@postgresql.org>
Sent: Friday, March 21, 2008 5:09 PM
Subject: Re: [BUGS] BUG #4047: case preserve for columns


> Eugen Konkov wrote:
>> Is there any workaround other then quoting to get working 1) and 2) as
>> expected?
>>
>
> The SQL standard actually says everything not quoted should be upshifted
> to upper case.   PostgreSQL opted for lower case a long time ago and has
> stuck with this for legacy.
>
> If your "100K lines of SQL" are autogenerated, why not have the
> autogenerator quote everything thats supposed to be case specific,
> alleviating any such problems ?

Re: BUG #4047: case preserve for columns

From

Date:

21 March 2008, 16:06:58

It is have no any matter to me if it is upshifted or lowershifted on server
sidethe standard does not specify that output of queries MUST be
lowershifted/upshifted.why you do this?

http://archives.postgresql.org/pgsql-hackers/2006-10/msg01527.php


----- Original Message -----
From: "John R Pierce" <pierce@hogranch.com>
To: "Eugen Konkov" <Eugen.Konkov@aldec.com>
Cc: <pgsql-bugs@postgresql.org>
Sent: Friday, March 21, 2008 5:09 PM
Subject: Re: [BUGS] BUG #4047: case preserve for columns


> Eugen Konkov wrote:
>> Is there any workaround other then quoting to get working 1) and 2) as
>> expected?
>>
>
> The SQL standard actually says everything not quoted should be upshifted
> to upper case.   PostgreSQL opted for lower case a long time ago and has
> stuck with this for legacy.
>
> If your "100K lines of SQL" are autogenerated, why not have the
> autogenerator quote everything thats supposed to be case specific,
> alleviating any such problems ?
>
>
>
>

From

Date:

21 March 2008, 16:08:30

Re: BUG #4047: case preserve for columns

From

Tom Lane

Date:

21 March 2008, 16:44:44

<Eugen.Konkov@aldec.com> writes:
> It is have no any matter to me if it is upshifted or lowershifted on server
> sidethe standard does not specify that output of queries MUST be
> lowershifted/upshifted.

Yes it does.  I quote SQL92 section 5.2 syntax rule 10:

            The <identifier body> of a <regular identifier> is equivalent
            to an <identifier body> in which every letter that is a lower-
            case letter is replaced by the equivalent upper-case letter
            or letters. This treatment includes determination of equiva-
            lence, representation in the Information and Definition Schemas,
            representation in the diagnostics area, and similar uses.

In particular "representation in the diagnostics area" would include the
case of column headings being returned to the client.

If you don't want case folding to happen, you need to use a quoted
identifier.  In the example you showed,

    SELECT "Id" AS ID, ...

it would have been sufficient to leave off the AS clause.

            regards, tom lane

Re: BUG #4047: case preserve for columns

From

Date:

21 March 2008, 16:56:23

SELECT "Id" AS ID
and this will return 'id' instead of 'ID'

may be is there server configurations variable to be case sensitive?
or return case preserved field names?
or may be I compile manually PostgreSQL to do that?

Because typing SELECT "Id" AS "ID" instead of SELECT ID is boring
else more your variant is less readable

If there no any way to migrate to PostgreSQL without changes application
it seems not good database because of it will too expensive for us to
migrate to (((


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: <Eugen.Konkov@aldec.com>
Cc: "John R Pierce" <pierce@hogranch.com>; <pgsql-bugs@postgresql.org>
Sent: Friday, March 21, 2008 6:43 PM
Subject: Re: [BUGS] BUG #4047: case preserve for columns


> <Eugen.Konkov@aldec.com> writes:
>> It is have no any matter to me if it is upshifted or lowershifted on
>> server
>> sidethe standard does not specify that output of queries MUST be
>> lowershifted/upshifted.
>
> Yes it does.  I quote SQL92 section 5.2 syntax rule 10:
>
>            The <identifier body> of a <regular identifier> is equivalent
>            to an <identifier body> in which every letter that is a lower-
>            case letter is replaced by the equivalent upper-case letter
>            or letters. This treatment includes determination of equiva-
>            lence, representation in the Information and Definition
> Schemas,
>            representation in the diagnostics area, and similar uses.
>
> In particular "representation in the diagnostics area" would include the
> case of column headings being returned to the client.
>
> If you don't want case folding to happen, you need to use a quoted
> identifier.  In the example you showed,
>
> SELECT "Id" AS ID, ...
>
> it would have been sufficient to leave off the AS clause.
>
> regards, tom lane

Re: BUG #4047: case preserve for columns

From

Date:

24 March 2008, 10:48:52

if standard cause head acheing ignore standard

You do so with UPPERCASE, so now you use all lowercased.
This is good when you do thing simpler.
But why you ignore asking of thousands people about case preserving?

create table test ( "Id" integer );
SELECT "Id" as "ID" FROM test

This cause BIG head acheing while moving to PG from other DBs
First of all this cause me and others write
SELECT "Id" as "ID" FROM test  ( cause to typing 8 + length(FieldName)
useless characters multiplied by field count)
Instead of
SELECT ID FROM test

Second it cause to write
SELECT
 id as "ID",
 name as "Name",
 phone as "Phone",
 ....
 adreass as "Address"
FROM test (typing tonn of useless characters)

instead of
SELECT * FROM test
This quiery rewriting is required because of hashes in programms are case
sensetive
and  '$row->{Name}' is not the same as '$row->{name}'
So when in my case MySQL returns 'ID', 'Name', 'Phone' when I executing
'SELECT * FROM' test query. I need rewrite it to
SELECT
 id as "ID",
 name as "Name",
 phone as "Phone",
 ....
 adreass as "Address"
FROM test

Instead of rewriting each reference to field '$row->{Name}'  to
'$row->{name}'
So I expand all my 'SELECT * FROM ' queries....

Third head acheing:
But now you can say: "STOP! Quote field names while creating tables"
But this will not stop our headaching, because of in scripts people had
writed
use
SELECT ID FROM or SELECT id FROM or SELECT Id FROM or maybe SELECT iD FROM
So this cause me to inspect each line in source files and do these:
SELECT "ID" FROM / SELECT "ID" as "id" FROM / SELECT "ID" as "Id" FROM
/SELECT "ID" as "iD" FROM
instead of rewrite each reference to field value in hash from '$row->{Name}'
=> '$row->{name}' etc
And all this scripts writed for years cause me and not ONLY ME!! big head
acheing ... :`-(

How do you think how much time this will take from me while moving from some
DB to PG? week? month?

And how much time will take to set up server variable as 'preserveCase= On'?
minute or two?

I google WWW and see answers: "case preserving will crash apps that have
been writed for years"
but my answer will "NO!" because of 'preserveCase' will be OFF by default
and this will not cause old apps to crash
They will get their lowercased field names as they get them before.

BUT 'preserveCase=ON' will make my app AND NOT ONLY MY APP!!! happy to get
field names as it used to get them

Developers, I know you do good work and you do best as you can.
You think about compativility and this is good and I go along with you
But can you go along with me and with other who moving from some DB to PG
and let them to switch ON preserveCase option and escape from those head
acheing described above.

Developers, lets do forward step as you do it with UPPERCASE changing it to
lowercase
I hope you will understand us who moveing to PG and, I hope, you will change
your mind about casePreserve

Thanks

PS: Any who had or has head aching or maybe see that 'casePreserve' DB
configuration option will be usefull or handy
VOTE for it

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: <Eugen.Konkov@aldec.com>
Cc: "John R Pierce" <pierce@hogranch.com>; <pgsql-bugs@postgresql.org>
Sent: Friday, March 21, 2008 6:43 PM
Subject: Re: [BUGS] BUG #4047: case preserve for columns

> <Eugen.Konkov@aldec.com> writes:
>> It is have no any matter to me if it is upshifted or lowershifted on
>> server
>> sidethe standard does not specify that output of queries MUST be
>> lowershifted/upshifted.
>
> Yes it does.  I quote SQL92 section 5.2 syntax rule 10:
>
>            The <identifier body> of a <regular identifier> is equivalent
>            to an <identifier body> in which every letter that is a lower-
>            case letter is replaced by the equivalent upper-case letter
>            or letters. This treatment includes determination of equiva-
>            lence, representation in the Information and Definition
> Schemas,
>            representation in the diagnostics area, and similar uses.
>
> In particular "representation in the diagnostics area" would include the
> case of column headings being returned to the client.
>
> If you don't want case folding to happen, you need to use a quoted
> identifier.  In the example you showed,
>
> SELECT "Id" AS ID, ...
>
> it would have been sufficient to leave off the AS clause.
>
> regards, tom lane

Re: BUG #4047: case preserve for columns

From

Date:

25 March 2008, 07:59:17

if standard cause head acheing ignore standard

You do so with UPPERCASE, so now you use all lowercased.
This is good when you do thing simpler.
But why you ignore asking of thousands people about case preserving?

create table test ( "Id" integer );
SELECT "Id" as "ID" FROM test

This cause BIG head acheing while moving to PG from other DBs
First of all this cause me and others write
SELECT "Id" as "ID" FROM test  ( cause to typing 8 + length(FieldName)
useless characters multiplied by field count)
Instead of
SELECT ID FROM test

Second it cause to write
SELECT
 id as "ID",
 name as "Name",
 phone as "Phone",
 ....
 adreass as "Address"
FROM test (typing tonn of useless characters)

instead of
SELECT * FROM test
This quiery rewriting is required because of hashes in programms are case
sensetive
and  '$row->{Name}' is not the same as '$row->{name}'
So when in my case MySQL returns 'ID', 'Name', 'Phone' when I executing
'SELECT * FROM' test query. I need rewrite it to
SELECT
 id as "ID",
 name as "Name",
 phone as "Phone",
 ....
 adreass as "Address"
FROM test

Instead of rewriting each reference to field '$row->{Name}'  to
'$row->{name}'
So I expand all my 'SELECT * FROM ' queries....

Third head acheing:
But now you can say: "STOP! Quote field names while creating tables"
But this will not stop our headaching, because of in scripts people had
writed
use
SELECT ID FROM or SELECT id FROM or SELECT Id FROM or maybe SELECT iD FROM
So this cause me to inspect each line in source files and do these:
SELECT "ID" FROM / SELECT "ID" as "id" FROM / SELECT "ID" as "Id" FROM
/SELECT "ID" as "iD" FROM
instead of rewrite each reference to field value in hash from '$row->{Name}'
=> '$row->{name}' etc
And all this scripts writed for years cause me and not ONLY ME!! big head
acheing ... :`-(

How do you think how much time this will take from me while moving from some
DB to PG? week? month?

And how much time will take to set up server variable as 'preserveCase= On'?
minute or two?

I google WWW and see answers: "case preserving will crash apps that have
been writed for years"
but my answer will "NO!" because of 'preserveCase' will be OFF by default
and this will not cause old apps to crash
They will get their lowercased field names as they get them before.

BUT 'preserveCase=ON' will make my app AND NOT ONLY MY APP!!! happy to get
field names as it used to get them

Developers, I know you do good work and you do best as you can.
You think about compativility and this is good and I go along with you
But can you go along with me and with other who moving from some DB to PG
and let them to switch ON preserveCase option and escape from those head
acheing described above.

Developers, lets do forward step as you do it with UPPERCASE changing it to
lowercase
I hope you will understand us who moveing to PG and, I hope, you will change
your mind about casePreserve

Thanks

PS: Any who had or has head aching or maybe see that 'casePreserve' DB
configuration option will be usefull or handy
VOTE for it

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: <Eugen.Konkov@aldec.com>
Cc: "John R Pierce" <pierce@hogranch.com>; <pgsql-bugs@postgresql.org>
Sent: Friday, March 21, 2008 6:43 PM
Subject: Re: [BUGS] BUG #4047: case preserve for columns

> <Eugen.Konkov@aldec.com> writes:
>> It is have no any matter to me if it is upshifted or lowershifted on
>> server
>> sidethe standard does not specify that output of queries MUST be
>> lowershifted/upshifted.
>
> Yes it does.  I quote SQL92 section 5.2 syntax rule 10:
>
>            The <identifier body> of a <regular identifier> is equivalent
>            to an <identifier body> in which every letter that is a lower-
>            case letter is replaced by the equivalent upper-case letter
>            or letters. This treatment includes determination of equiva-
>            lence, representation in the Information and Definition
> Schemas,
>            representation in the diagnostics area, and similar uses.
>
> In particular "representation in the diagnostics area" would include the
> case of column headings being returned to the client.
>
> If you don't want case folding to happen, you need to use a quoted
> identifier.  In the example you showed,
>
> SELECT "Id" AS ID, ...
>
> it would have been sufficient to leave off the AS clause.
>
> regards, tom lane