Обсуждение: BUG #5308: How to disable Case sensitivity on naming identifiers
The following bug has been logged online: Bug reference: 5308 Logged by: Kelly SACAULT Email address: kelly.sacault@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
On Tue, Feb 2, 2010 at 12:11 PM, Kelly SACAULT <kelly.sacault@gmail.com> wr= ote: > > The following bug has been logged online: > > Bug reference: =A0 =A0 =A05308 > Logged by: =A0 =A0 =A0 =A0 =A0Kelly SACAULT > Email address: =A0 =A0 =A0kelly.sacault@gmail.com > PostgreSQL version: 8.4.2 > Operating system: =A0 Ubuntu 9.10 > Description: =A0 =A0 =A0 =A0How to disable Case sensitivity on naming ide= ntifiers > 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 h= ave > spent many hours in studying the parameters, the faqs and the forums. I h= ave > 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 ? I thought PgSQL was case insensitive by default and that both those would be executed as: SELECT col1 FROM mytable; If you are seeing otherwise in the manual, can you provide a section? Best Wishes, Chris Travers > > Kelly > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
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=3D# create table Foo(a integer);
CREATE TABLE
Time: 174,078 ms
postgres=3D# select * from Foo;
a
---
(0 rows)
Time: 33,255 ms
postgres=3D# select * from foo;
a
---
(0 rows)
Time: 0,822 ms
postgres=3D# drop table foo;
DROP TABLE
Time: 34,945 ms
postgres=3D# create table "Foo"(a integer);
CREATE TABLE
Time: 3,225 ms
postgres=3D# select * from foo;
ERROR: relation "foo" does not exist
LINE 1: select * from foo;
^
postgres=3D# select * from Foo;
ERROR: relation "foo" does not exist
LINE 1: select * from Foo;
^
postgres=3D# 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.sacault@gmail.com>:
>
> The following bug has been logged online:
>
> Bug reference: =C2=A0 =C2=A0 =C2=A05308
> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Kelly SACAULT
> Email address: =C2=A0 =C2=A0 =C2=A0kelly.sacault@gmail.com
> PostgreSQL version: 8.4.2
> Operating system: =C2=A0 Ubuntu 9.10
> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0How to disable Case sensitivity o=
n 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 h=
ave
> spent many hours in studying the parameters, the faqs and the forums. I h=
ave
> 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
>
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=E9ma | Nom | Type | Propri=E9taire --------+----------------+-------+-------------- public | *foo * | table | postgres (1 lignes) # select * from FOO; *nom * ----- (0 ligne) # select * from "Foo"; ERREUR: la relation =AB Foo =BB 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 =3D 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=E9ma | Nom | Type | Propri=E9taire --------+----------------+-------+-------------- 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.stehule@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=3D# create table Foo(a integer); > CREATE TABLE > Time: 174,078 ms > postgres=3D# select * from Foo; > a > --- > (0 rows) > > Time: 33,255 ms > postgres=3D# select * from foo; > a > --- > (0 rows) > > Time: 0,822 ms > postgres=3D# drop table foo; > DROP TABLE > Time: 34,945 ms > postgres=3D# create table "Foo"(a integer); > CREATE TABLE > Time: 3,225 ms > postgres=3D# select * from foo; > ERROR: relation "foo" does not exist > LINE 1: select * from foo; > ^ > postgres=3D# select * from Foo; > ERROR: relation "foo" does not exist > LINE 1: select * from Foo; > ^ > postgres=3D# 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.sacault@gmail.com>: > > > > The following bug has been logged online: > > > > Bug reference: 5308 > > Logged by: Kelly SACAULT > > Email address: kelly.sacault@gmail.com > > PostgreSQL version: 8.4.2 > > Operating system: Ubuntu 9.10 > > Description: How to disable Case sensitivity on naming identifie= rs > > Details: > > > > I have installed Postgresql using Ubuntu Synaptic. > > > > In the contrary of what is stated in the official manual, I have to wri= te > > 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 > > >
On Wed, Feb 3, 2010 at 2:13 PM, Kelly SACAULT <kelly.sacault@gmail.com> wrote: > 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 It's completely true. Identifiers are most definitely case-insensitive, unless of course you quote them. This is documented on the very same web page you just quoted, a little far down: Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.) You may not like the current behavior (that's up to you), but I don't believe there's any problem with how it's documented. ...Robert
Kelly SACAULT <kelly.sacault@gmail.com> writes: > 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..." You need to not stop reading at that point, but continue on to the part that explains how quoted identifiers work. regards, tom lane
On Wed, Feb 3, 2010 at 11:30 AM, Robert Haas <robertmhaas@gmail.com> wrote: > Quoting an identifier also makes it case-sensitive, whereas unquoted > names are always folded to lower case. For example, the identifiers > FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" > and "FOO" are different from these three and each other. (The folding > of unquoted names to lower case in PostgreSQL is incompatible with the > SQL standard, which says that unquoted names should be folded to upper > case. Thus, foo should be equivalent to "FOO" not "foo" according to > the standard. If you want to write portable applications you are > advised to always quote a particular name or never quote it.) > > You may not like the current behavior (that's up to you), but I don't > believe there's any problem with how it's documented. There might actually be two reasons to document the folding-to-lower though: 1) The SQL standards mandate folding to upper instead, so this is a deviation from the standard (a good one IMO), but it might be useful to highlight it for the reader esp. since it will hit those trying to support multiple databases. 2) While I doubt that too many people get stuck on that (I did.... for all of 30 seconds), more clarity might be helpful for individuals just starting to pick up PostgreSQL. I don't like the proposed wording though. I would suggest something more like: "Unless double-quoted, all identifiers are folded to lower case, making comparisons generally case insensitive. The SQL standard mandates folding identifiers to upper case, but the consensus among the PostgreSQL development team is that folding to lower case is better. If double-quotes are not used ever, or are used consistently throughout the application, this poses no compatibility problems in terms of SQL queries."
On Wed, Feb 3, 2010 at 11:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Kelly SACAULT <kelly.sacault@gmail.com> writes: >> 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..." > > You need to not stop reading at that point, but continue on to the part > that explains how quoted identifiers work. The only issue here (not a major one, maybe not even worth fixing, but probably worth mentioning) is that the discussion of case folding is more than a screen away and that it isn't immediately clear that there is more discussion. It is probably understandable that some people would miss it (I did, a moment ago, until you mentioned it). A simple (see below) might be a good idea. Then again it might be a good thing for someone else (not generally engrossed in development on the project) to submit a patch for :-) Best Wishes, Chris Travers
Chris Travers <chris@metatrontech.com> writes:
> On Wed, Feb 3, 2010 at 11:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Kelly SACAULT <kelly.sacault@gmail.com> writes:
>>> ".. Identifier and key word names are case insensitive..."
>>
>> You need to not stop reading at that point, but continue on to the part
>> that explains how quoted identifiers work.
> The only issue here (not a major one, maybe not even worth fixing, but
> probably worth mentioning) is that the discussion of case folding is
> more than a screen away and that it isn't immediately clear that there
> is more discussion. It is probably understandable that some people
> would miss it (I did, a moment ago, until you mentioned it). A simple
> (see below) might be a good idea.
Or we could rephrase, perhaps
"Key words and unquoted identifiers are case insensitive..."
which would at least cue people that there's more to learn. We can't
try to explain every detail in the first sentence though.
> Then again it might be a good
> thing for someone else (not generally engrossed in development on the
> project) to submit a patch for :-)
Yeah ... those of us who've already learned this stuff are probably not
able to see it with a novice's eyes.
regards, tom lane
Chris Travers <chris@metatrontech.com> wrote: > It is probably understandable that some people > would miss it (I did, a moment ago, until you mentioned it). That seems like pretty good evidence that a footnote or qualification of the initial statement would occasionally save some confusion. -Kevin
Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Key words and unquoted identifiers are case insensitive..." FWIW, that is the *exact* rewording that came to mind for me as a possible solution. -Kevin
On Wed, Feb 3, 2010 at 2:36 PM, Chris Travers <chris@metatrontech.com> wrot= e: > I don't like the proposed wording though. =A0I would suggest something mo= re like: Just to be clear, that's the actual wording, not a proposal. ...Robert
Robert Haas wrote: > On Wed, Feb 3, 2010 at 2:36 PM, Chris Travers <chris@metatrontech.com> wrote: > > I don't like the proposed wording though. ?I would suggest something more like: > > Just to be clear, that's the actual wording, not a proposal. I found one place in the docs where this wasn't immediately clear, so I applied the attached documentation patch. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/syntax.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v retrieving revision 1.139 diff -c -c -r1.139 syntax.sgml *** doc/src/sgml/syntax.sgml 15 Dec 2009 17:57:46 -0000 1.139 --- doc/src/sgml/syntax.sgml 3 Feb 2010 22:42:38 -0000 *************** *** 144,150 **** <primary>case sensitivity</primary> <secondary>of SQL commands</secondary> </indexterm> ! Identifier and key word names are case insensitive. Therefore: <programlisting> UPDATE MY_TABLE SET A = 5; </programlisting> --- 144,150 ---- <primary>case sensitivity</primary> <secondary>of SQL commands</secondary> </indexterm> ! Unquoted identifier and key word names are case insensitive. Therefore: <programlisting> UPDATE MY_TABLE SET A = 5; </programlisting>
Bruce Momjian escribió: > I found one place in the docs where this wasn't immediately clear, so I > applied the attached documentation patch. I liked Tom's suggestion better, because then you don't start questioning about quoting key words or not. > --- 144,150 ---- > <primary>case sensitivity</primary> > <secondary>of SQL commands</secondary> > </indexterm> > ! Unquoted identifier and key word names are case insensitive. Therefore: > <programlisting> > UPDATE MY_TABLE SET A = 5; > </programlisting> -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Bruce Momjian escribió:
>> I found one place in the docs where this wasn't immediately clear, so I
>> applied the attached documentation patch.
> I liked Tom's suggestion better, because then you don't start
> questioning about quoting key words or not.
Yes, exactly, that was why I changed the ordering. I had always thought
that the reference to "names" of keywords was a bit off-key, too.
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > Bruce Momjian escribió: > >> I found one place in the docs where this wasn't immediately clear, so I > >> applied the attached documentation patch. > > > I liked Tom's suggestion better, because then you don't start > > questioning about quoting key words or not. > > Yes, exactly, that was why I changed the ordering. I had always thought > that the reference to "names" of keywords was a bit off-key, too. Yea, I like Tom's wording better too. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Hi everybody,
Many thanks for all your comments.
It appears that clarity is very important in a user manual. I like both
Chris and Tom 's expression.
Chris :
*"Unless double-quoted, all identifiers are folded to lower case,
making comparisons generally case insensitive. The SQL standard
mandates folding identifiers to upper case, but the consensus among
the PostgreSQL development team is that folding to lower case is
better. If double-quotes are not used ever, or are used consistently
throughout the application, this poses no compatibility problems in
terms of SQL queries."
*
Tom :
*"Key words and unquoted identifiers are case insensitive..."*
Personally, I prefer the Chris's one as it is more precise and detailed on
what PostgreSQL is doing when it processes SQL statements.
*
Now knowing that particularity, I will use PostgreSQL accordingly*.
At last, regarding SQL standards, in future releases of PostgreSQL, I would
like to remind my wish to see the suggested feature beside the "lower case
folding chosen by PostgreSQL dev. team".
Let see this example :
CREATE TABLE "EdtIndexCheck"
(
"Id" integer NOT NULL,
"SiteId" integer,
"CheckDate" timestamp without time zone,
"IndexIn" integer,
"IndexOut" integer,
"AmountBilled" double precision,
CONSTRAINT "PK_EdtIndexCheck" PRIMARY KEY ("Id")
)
When browsing tables and columns in pgadmin or psql, I see this (that is
very fine and good reading) :
EdtIndexCheck
Id
SiteId
CheckDate
IndexIn
IndexOut
AmountBilled
... and would be allowed to write queries anyway without having to use the
double-quotes like :
SELECT id, siteId, IndexIn, IndexOut, AmountBilled FROM EdtIndexCheck;
As as result, I get a friendly reading in pgadmin or psql, and have an
easier and quicker way in expressing sql statements.
I agree this feature is *not essential* but is definitely a *friendly
one*as seen in other SGBDR. This feature could be optional in other to
preserve
compatibility.
Very best regards,
Kelly
2010/2/3 Bruce Momjian <bruce@momjian.us>
> Tom Lane wrote:
> > Alvaro Herrera <alvherre@commandprompt.com> writes:
> > > Bruce Momjian escribi=F3:
> > >> I found one place in the docs where this wasn't immediately clear, so
> I
> > >> applied the attached documentation patch.
> >
> > > I liked Tom's suggestion better, because then you don't start
> > > questioning about quoting key words or not.
> >
> > Yes, exactly, that was why I changed the ordering. I had always thought
> > that the reference to "names" of keywords was a bit off-key, too.
>
> Yea, I like Tom's wording better too.
>
> --
> Bruce Momjian <bruce@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>