Thread: Case sensitive field names

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.

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.

// Jarmo

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