Case sensitive field names

Jarmo Paavilainen wrote:

Hi,

Is there a way to make postgre insensitive about field name cases?

Like "initdb --fields-are-case-insensitive --compares-are-case-insensitive"

Yes I know about "CaseIsKept" and CaseIsNotKept (note the quotes). But that
gives me more trouble than it solves. And what about "case insensitive field
name with spaces". I believe that space is legal in field names.

The main problem I see with case-insensitivity is the fact that there
are always
more than one way to do it, as it depends on charset _and_ locale ;(

For example '�'=='�' in my locale but not in US, not to mention that in
some
locales even the character count may change when going from upper to
lower case.

So I suspect that only valid reason for case-insensitivity is
compatibility with
arbitraryly-case-altering OS-es, like the ones Microsoft produces.

For any other use WYSIWYG field names should be preferred.

Are there any real reason why postgre is sensitive about field names (except
SQL92 states that this is how it must be)?

I suppose somewhere along the way I have all field names separated from the
query, and in which file(s) does that happen? (So I can do my own hack, add
"tolower(fieldName)").

Ive tried to locate the right files in the source for 7.0.2, but there are
more that one file.

I guess the best place would be sobewhere very near lexer.

You could also try just uppercasing anything outside ''/"" even before
it is
passed to backend.

---------
Hannu

...

Is there a way to make postgre insensitive about field name cases?

Like

"initdb --fields-are-case-insensitive --compares-are-case-insensitive"
...

The main problem I see with case-insensitivity is the fact that there
are always more than one way to do it, as it depends on charset _and_

locale ;(

For example '�'=='�' in my locale but not in US, not to mention that in
some locales even the character count may change when going from upper to
lower case.

Thats not really a problem with field names. *I think* you should always use
ASCII chars in fieldnames (and only those between 32 (space) and 'z'.

And PostgreSQL should cope with case insensitive search. If not, then I can
not use it.

Can PostgreSQL do a case insensitive search?

...

arbitraryly-case-altering OS-es, like the ones Microsoft produces.

Yeah and microsoft SQL server can do a case insensitive search, so can
Sybase (at least the Win versions).

...

query, and in which file(s) does that happen? (So I can do my own hack,

add

"tolower(fieldName)").

...

I guess the best place would be sobewhere very near lexer.

Ill look for a good spot.

You could also try just uppercasing anything outside ''/"" even before
it is passed to backend.

No good, because field values should keep case (even if you search on them
case insensitive). But then again to use " as a field value delimiter is
illegal, isnt it?

// Jarmo

Jarmo Paavilainen wrote:

...

Is there a way to make postgre insensitive about field name cases?

Like

"initdb --fields-are-case-insensitive --compares-are-case-insensitive"
...

The main problem I see with case-insensitivity is the fact that there
are always more than one way to do it, as it depends on charset _and_

locale ;(

For example '�'=='�' in my locale but not in US, not to mention that in
some locales even the character count may change when going from upper to
lower case.

Thats not really a problem with field names. *I think* you should always use

What do you mean by "should" ;)

ASCII chars in fieldnames (and only those between 32 (space) and 'z'.

hannu=> create table "b�v"("gbz�h" int);
CREATE
hannu=> \d b�v
Table    = b�v
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| gbz�h                            | int4                            
|     4 |
+----------------------------------+----------------------------------+-------+

And PostgreSQL should cope with case insensitive search. If not, then I can
not use it.

Can PostgreSQL do a case insensitive search?

Postgres can do CI regular expressions :

select * from books where title ~* '.*Tom.*');

case insensitive LIKE is not directly supported , but you can do
something like

select * from books where upper(title) LIKE upper('%Tom%');

...

arbitraryly-case-altering OS-es, like the ones Microsoft produces.

Yeah and microsoft SQL server can do a case insensitive search, so can
Sybase (at least the Win versions).

IIRC, MSSQL == Sybase (at least older versions of MSSQL)

You could also try just uppercasing anything outside ''/"" even before
it is passed to backend.

No good, because field values should keep case (even if you search on them
case insensitive). But then again to use " as a field value delimiter is
illegal, isnt it?

I understood that you wanted field _names_ to be case-insensitive, not
field values.

Field names are delimited by "", values of type string by ''

---------------
Hannu