Re: BUG #5308: How to disable Case sensitivity on naming identifiers

Hi Pavel, and Chris,

Thank you both your yours responses.

Here is what I read from the officiel manual :
http://www.postgresql.org/docs/8.0/static/sql-syntax.html

stating that :
".. Identifier and key word names are case insensitive..."

This is not the truth at all and I think that this statement must be
corrected in the manual regarding the below observation ; And thus this may
save many hours of search for many readers who get stuck at this point.

Here is my test using psql :

# create table *Foo*(*Nom* integer);

CREATE TABLE
# \dt
Liste des relations
Schéma | Nom | Type | Propriétaire
--------+----------------+-------+--------------
public | *foo * | table | postgres
(1 lignes)

# select * from FOO;
*nom *
-----
(0 ligne)

# select * from "Foo";
ERREUR: la relation « Foo » n'existe pas
LIGNE 1 : select * from "Foo"

In this example, I have executed a CREATE statement with identifiers names
including some uppercases and typed without double quotes. I would like much
to have those identifiers displayed as wished in the CREATE statement for
easy and friendly reading.

But what I can notice is that Postgresql does this when double quotes are
not used :
All identifiers are lowercased in all sql statements before those are
executed.
To prevent the 'lowercasing', the identifiers must be double quoted in DML
and SELECT queries.

In my opinion, I would suggest to put int the official manual the following
note :

"Key word names are case insensitive, but identifiers names are always case
sensitive. If uppercases are wanted in the identifiers, those identifiers
must be double quoted in all sql statements. In order to make the
identifiers behave as 'case insensitive' in sql statement, the identifiers
must not be double quoted or must be lowercased."

My suggestion in the major release of Postgresql :
A new parameter in Postgresql configuration file, (e.g.: queryident_ci =
Yes[|No]). This parameter would tell postgresql if the identifiers are case
sensitive or case insensitive (default). If the default mode is used, the
result would be that :

# create table *Foo*(*Nom* integer);

CREATE TABLE
# \dt
Liste des relations
Schéma | Nom | Type | Propriétaire
--------+----------------+-------+--------------
public | *Foo * | table | postgres
(1 lignes)

# select * from *foo*;
*Nom *
-----
(0 ligne)

This is much more friendly to read and easy in making queries without the
tricky constraint of using the double quotes.

I have this friendly behavior in MS SQL Server. And I have seen many people
complaining regarding the current behavior in my google searches.

So, Hope this suggestion will be observed in future release...

Very best regards,

Kelly

2010/2/2 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>

> Hello,
>
> usually sql identifiers are case insensitive. There are exception. If
> you use double quotes for sql identifier, then you have to write exact
> same identifier everywhere.
>
> postgres=# create table Foo(a integer);
> CREATE TABLE
> Time: 174,078 ms
> postgres=# select * from Foo;
> a
> ---
> (0 rows)
>
> Time: 33,255 ms
> postgres=# select * from foo;
> a
> ---
> (0 rows)
>
> Time: 0,822 ms
> postgres=# drop table foo;
> DROP TABLE
> Time: 34,945 ms
> postgres=# create table "Foo"(a integer);
> CREATE TABLE
> Time: 3,225 ms
> postgres=# select * from foo;
> ERROR: relation "foo" does not exist
> LINE 1: select * from foo;
> ^
> postgres=# select * from Foo;
> ERROR: relation "foo" does not exist
> LINE 1: select * from Foo;
> ^
> postgres=# select * from "Foo";
> a
> ---
> (0 rows)
>
> Time: 1,277 ms
>
> you cannot change this behave. Just don't use double quotes in create
> statement.
>
> Regards
> Pavel Stehule
>
>
>
> 2010/2/2 Kelly SACAULT <kelly(dot)sacault(at)gmail(dot)com>:
> >
> > The following bug has been logged online:
> >
> > Bug reference: 5308
> > Logged by: Kelly SACAULT
> > Email address: kelly(dot)sacault(at)gmail(dot)com
> > PostgreSQL version: 8.4.2
> > Operating system: Ubuntu 9.10
> > Description: How to disable Case sensitivity on naming identifiers
> > Details:
> >
> > I have installed Postgresql using Ubuntu Synaptic.
> >
> > In the contrary of what is stated in the official manual, I have to write
> > case sensitive SQL statements in my postgresql connexion.
> >
> > What parameter do I have to change in the postgresaql configuration ? I
> have
> > spent many hours in studying the parameters, the faqs and the forums. I
> have
> > found nothing to make my SQL statements case-insensitive.
> > I want to be able to execute successfully such stmts:
> >
> > SELECT col1 FROM myTABLE
> >
> > SELECT Col1 FROM myTable
> >
> > please, may you help ?
> >
> > Kelly
> >
> > --
> > Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-bugs
> >
>