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.steh...@gmail.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.saca...@gmail.com>: > > > > The following bug has been logged online: > > > > Bug reference: 5308 > > Logged by: Kelly SACAULT > > Email address: kelly.saca...@gmail.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@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-bugs > > >
