Thread: quoted identifier behaviour

Are there plans to make Postgresql's behavior SQL compliant with regards
to quoted identifiers?  My specific need is to access tables named in
lower case by an uppercase quoted identifier, which is in line with the
SQL standard.  So if I created table foo, I should be able to access it
like "FOO".  Is there a configuration options that modifies this
behavior or can I change the names in the system tables?

Randall

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> Are there plans to make Postgresql's behavior SQL compliant with regards
> to quoted identifiers?

No.

> My specific need is to access tables named in lower case by an uppercase
> quoted identifier, which is in line with the SQL standard.  So if I created
> table foo, I should be able to access it like "FOO".  Is there a configuration
> options that modifies this behavior or can I change the names in the system tables?

No configuration option, but you can always rename tables with

ALTER TABLE foo RENAME TO "FOO"

Simple creating the table as "FOO" in the first place avoids the problem as well.
Be aware that using uppercase identifiers means that *all* access to that table must
now quote the name.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200703141312
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFF+C2PvJuQZxSWSsgRAw/XAJ9gE9lskT7yBQUbJsIa8mSPyxmc/wCgrFJx
EWX65PmQaWUIr5MCDykXJN4=
=LyWH
-----END PGP SIGNATURE-----

Randall Smith wrote:
> Are there plans to make Postgresql's behavior SQL compliant with
regards
> to quoted identifiers?  My specific need is to access tables named in
> lower case by an uppercase quoted identifier, which is in line with
the
> SQL standard.  So if I created table foo, I should be able to access
it
> like "FOO".  Is there a configuration options that modifies this
> behavior or can I change the names in the system tables?

I don't think that there are plans to change this as it would be
hard to maintain compatibility.

You must not change the names of system tables, but you can
certainly create upper case views for them.

Yours,
Laurenz Albe

Albe Laurenz wrote:
> Randall Smith wrote:
>> Are there plans to make Postgresql's behavior SQL compliant with
> regards
>> to quoted identifiers?  My specific need is to access tables named in
>> lower case by an uppercase quoted identifier, which is in line with
> the
>> SQL standard.  So if I created table foo, I should be able to access
> it
>> like "FOO".  Is there a configuration options that modifies this
>> behavior or can I change the names in the system tables?
>
> I don't think that there are plans to change this as it would be
> hard to maintain compatibility.

It would be nice to have it as a configuration option though.  That way,
you could have both SQL compliance and backward compatibility.

I'm trying to get Postgresql to work with software that uses JDBC and
Oracle for a large government project.  So I have to report that the
application won't work with Postgresql because it (PG) doesn't adhere to
the standard.  That's usually something I say about Oracle and MySQL.
I'm an advocate of Postgresql and usually tout SQL compliance as a
strength, so it bothers me that this is not in line to be corrected.

>
> You must not change the names of system tables, but you can
> certainly create upper case views for them.

That's my current solution.  I imagine this is not very efficient, but I
could be wrong.

Thanks.

Randall

>
> Yours,
> Laurenz Albe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/
>

Randall Smith <randall@tnr.cc> writes:
> I'm an advocate of Postgresql and usually tout SQL compliance as a
> strength, so it bothers me that this is not in line to be corrected.

It's not that it's not on the radar screen, it's just that no one sees
a way to do it that's going to be acceptable.  We're not willing to give
up the current behavior, both for backwards-compatibility reasons and
because most of us just plain like it better (ALL UPPER CASE IS UGLY AND
HARDER TO READ...).  So we'd need to support both, and that's hard.
Easy answers like "make it a configuration option" don't work because
they break too much stuff, including a whole lot of client-side code
that we don't control.

There are a couple of long threads in the pghackers archives discussing
pros and cons of different possibilities, if you're interested.

            regards, tom lane

Tom Lane wrote:
> Randall Smith <randall@tnr.cc> writes:
>> I'm an advocate of Postgresql and usually tout SQL compliance as a
>> strength, so it bothers me that this is not in line to be corrected.
>
> It's not that it's not on the radar screen, it's just that no one sees
> a way to do it that's going to be acceptable.  We're not willing to give
> up the current behavior, both for backwards-compatibility reasons and
> because most of us just plain like it better (ALL UPPER CASE IS UGLY AND
> HARDER TO READ...).  So we'd need to support both, and that's hard.
> Easy answers like "make it a configuration option" don't work because
> they break too much stuff, including a whole lot of client-side code
> that we don't control.
>
> There are a couple of long threads in the pghackers archives discussing
> pros and cons of different possibilities, if you're interested.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Thanks Tom.  I understand your points and I gather that the cost of
making the change (even as an option) outweighs the benefits of SQL
conformance for the developers.  Though I'm still of the same opinion.

I'll give the pghackers forum a visit and since I'm already on the
subject here, I'll make a direct comparison of the situation.
Microsoft's Internet Explorer web browser is known to have poor support
for the CSS standard, but refuses to fix it saying that it's too
difficult and would break existing websites.  Many developers, myself
included, prefer to code to the standard and have the html/css render
correctly in all browsers.  In the long run, it's better to do it right
even if that means breaking things today. If the standard is good (there
are poor standards), there's no reason to not use the standard.

Thanks again.

Randall

Randall Smith wrote on 14.03.2007 18:59:
> I'm trying to get Postgresql to work with software that uses JDBC and
> Oracle for a large government project.  So I have to report that the
> application won't work with Postgresql because it (PG) doesn't adhere to
> the standard.  That's usually something I say about Oracle and MySQL.
> I'm an advocate of Postgresql and usually tout SQL compliance as a
> strength, so it bothers me that this is not in line to be corrected.

Then the software is not written well :)

DatabaseMetaData.storesLowerCaseIdentifiers() correctly tells the program that
PG stores everything in lowercase. So if a (JDBC based) software wants to be
truly multi-DBMS enabled, it *has* to retrieve those things from the driver.

Thomas

This is the last statement I found on the issue, which is someone hopeful.

http://archives.postgresql.org/pgsql-hackers/2006-11/msg00347.php

Randall

Thomas Kellerer wrote:
> Randall Smith wrote on 14.03.2007 18:59:
>> I'm trying to get Postgresql to work with software that uses JDBC and
>> Oracle for a large government project.  So I have to report that the
>> application won't work with Postgresql because it (PG) doesn't adhere
>> to the standard.  That's usually something I say about Oracle and
>> MySQL. I'm an advocate of Postgresql and usually tout SQL compliance
>> as a strength, so it bothers me that this is not in line to be corrected.
>
> Then the software is not written well :)
>
> DatabaseMetaData.storesLowerCaseIdentifiers() correctly tells the
> program that PG stores everything in lowercase. So if a (JDBC based)
> software wants to be truly multi-DBMS enabled, it *has* to retrieve
> those things from the driver.
>
> Thomas
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

I don't write Java software so I can't discuss what you said.  However,
the SQL the app sends IS SQL compliant and Postgresql is not.  A table
named foo should be accessible as both foo and "FOO" according to the
standard.

Randall

----- Original Message -----
From: "Randall Smith" <randall@tnr.cc>
>
> I'll give the pghackers forum a visit and since I'm already on the subject
> here, I'll make a direct comparison of the situation. Microsoft's Internet
> Explorer web browser is known to have poor support for the CSS standard,
> but refuses to fix it saying that it's too difficult and would break
> existing websites.  Many developers, myself included, prefer to code to
> the standard and have the html/css render correctly in all browsers.  In
> the long run, it's better to do it right even if that means breaking
> things today. If the standard is good (there are poor standards), there's
> no reason to not use the standard.
>
In principle, I don't disagree with you.  However, I tend to be much more
pragmatic.  Often we have little choice in the tools we use.  I have seen
occasions where developers were constrained to use MS Visual Studio.  It is
an outstanding product now (I have, and use the professional edition of MS
VS 2005).  However, MS VS v6 was mediocre at best.   And a majority of WIN32
developers had to use it.  It was popular despite its problems.  It had very
poor compliance to the ANSI C++ standards at the turn of the century,
although that isn't too surprising given its age.  But many people thought
they were writing standards compliant code, and that code broke big time
once they tried to use it with later, better, compilers.  That is just one
example.   Yours is another, and there examples relating to FORTRAN (is
there anyone other than me old enough to remember that? - I recently
encountered IT professionals who didn't know what punch cards where or how
they were used). Yet much FORTRAN code that is four or five decades old is
still valuable a) because it was written to a standard and b) there are
tools to automate converting it into C, allowing it to be used while an old
C++ programmer like myself adapts it to take advantage of key C++
programming idioms and language features (templates, template
metaprogramming).  This is because in many cases the core algorithms
developed at the time have not been supplanted by better ones, and they are
still essential for intensive number crunching.  I have seen technologies
and standards come and go (or better, be upgraded), and while we ought to
write our code to be as compliant as possible to the relevant standards, if
there is one (there isn't one, last I checked, for VB, Java or Perl), in the
end our tools are the final arbiters of what is legal and what isn't.
Therefore, when the investment in the code in question is significant, and
only then, the most viable approach to trying to preserve the value of the
code is to use the highest common factor between the standard and what the
tools say the standard is (it isn't unusual for tool developers to disagree
about what the standard really means, especially when dealing with some of
the harder issues, and nothing is as simple as it appears at first glance).

It is important to remember that standards change.  Just compare the
different versions of the ANSI standard for SQL that have existed over the
years.  Or do the same for some of the other languages such as FORTRAN, C
and C++.  Since it is certain that even the best standards will change, and
that some of those changes will break existing code, I do not believe it is
worth getting paranoid or worried or upset just because one or another of
our favourite tools lacks perfect compliance with the standard.  I do not
define what is right by what a standard has to say.  Rather, I define it
according to whether or not the correct answers are obtained and whether or
not the application does for the user what the user needs it to do.  In this
view, then, there are multiple right options, and this takes us back to our
tools being the final arbiter of whether or not our code is correct.

So, I say that good standards are valuable tools that can be used to
preserve the value of code that carries significant investment.  At the same
time, they are only tools and the value they provide may well pale relative
to other considerations.  I disagree with you when you say there's no reason
not to use the (good) standard.  Often tool developers put considerable
effort into providing features in addition to what the standard specifies,
often without compromising compliance to the standard since often details
are specified to be implementation dependant.  Regardless of their motives
for doing so, there are good, economic and practical reasons to use such
features.  The trick, if one must worry about using the code fifty years
from now, is to ensure that implementation specific code is well separated
from standard compliant code and that it is well documented.  That way, when
the technology evolves in a way that will likely break your code (as the
coming demise of MFC will certainly do to countless WIN32 applications), it
becomes easier to replace what is broken and preserve what is not broken.

HTH

Ted

Ted Byers wrote:
>
> ----- Original Message ----- From: "Randall Smith" <randall@tnr.cc>
>>
>> I'll give the pghackers forum a visit and since I'm already on the
>> subject here, I'll make a direct comparison of the situation.
>> Microsoft's Internet Explorer web browser is known to have poor
>> support for the CSS standard, but refuses to fix it saying that it's
>> too difficult and would break existing websites.  Many developers,
>> myself included, prefer to code to the standard and have the html/css
>> render correctly in all browsers.  In the long run, it's better to do
>> it right even if that means breaking things today. If the standard is
>> good (there are poor standards), there's no reason to not use the
>> standard.
>>
> In principle, I don't disagree with you.  However, I tend to be much
> more pragmatic.  Often we have little choice in the tools we use.  I
> have seen occasions where developers were constrained to use MS Visual
> Studio.  It is an outstanding product now (I have, and use the
> professional edition of MS VS 2005).  However, MS VS v6 was mediocre at
> best.   And a majority of WIN32 developers had to use it.  It was
> popular despite its problems.  It had very poor compliance to the ANSI
> C++ standards at the turn of the century, although that isn't too
> surprising given its age.  But many people thought they were writing
> standards compliant code, and that code broke big time once they tried
> to use it with later, better, compilers.  That is just one example.
> Yours is another, and there examples relating to FORTRAN (is there
> anyone other than me old enough to remember that? - I recently
> encountered IT professionals who didn't know what punch cards where or
> how they were used). Yet much FORTRAN code that is four or five decades
> old is still valuable a) because it was written to a standard and b)
> there are tools to automate converting it into C, allowing it to be used
> while an old C++ programmer like myself adapts it to take advantage of
> key C++ programming idioms and language features (templates, template
> metaprogramming).  This is because in many cases the core algorithms
> developed at the time have not been supplanted by better ones, and they
> are still essential for intensive number crunching.  I have seen
> technologies and standards come and go (or better, be upgraded), and
> while we ought to write our code to be as compliant as possible to the
> relevant standards, if there is one (there isn't one, last I checked,
> for VB, Java or Perl), in the end our tools are the final arbiters of
> what is legal and what isn't. Therefore, when the investment in the code
> in question is significant, and only then, the most viable approach to
> trying to preserve the value of the code is to use the highest common
> factor between the standard and what the tools say the standard is (it
> isn't unusual for tool developers to disagree about what the standard
> really means, especially when dealing with some of the harder issues,
> and nothing is as simple as it appears at first glance).
>
> It is important to remember that standards change.  Just compare the
> different versions of the ANSI standard for SQL that have existed over
> the years.  Or do the same for some of the other languages such as
> FORTRAN, C and C++.  Since it is certain that even the best standards
> will change, and that some of those changes will break existing code, I
> do not believe it is worth getting paranoid or worried or upset just
> because one or another of our favourite tools lacks perfect compliance
> with the standard.  I do not define what is right by what a standard has
> to say.  Rather, I define it according to whether or not the correct
> answers are obtained and whether or not the application does for the
> user what the user needs it to do.  In this view, then, there are
> multiple right options, and this takes us back to our tools being the
> final arbiter of whether or not our code is correct.
>
> So, I say that good standards are valuable tools that can be used to
> preserve the value of code that carries significant investment.  At the
> same time, they are only tools and the value they provide may well pale
> relative to other considerations.  I disagree with you when you say
> there's no reason not to use the (good) standard.  Often tool developers
> put considerable effort into providing features in addition to what the
> standard specifies, often without compromising compliance to the
> standard since often details are specified to be implementation
> dependant.  Regardless of their motives for doing so, there are good,
> economic and practical reasons to use such features.  The trick, if one
> must worry about using the code fifty years from now, is to ensure that
> implementation specific code is well separated from standard compliant
> code and that it is well documented.  That way, when the technology
> evolves in a way that will likely break your code (as the coming demise
> of MFC will certainly do to countless WIN32 applications), it becomes
> easier to replace what is broken and preserve what is not broken.
>
> HTH
>
> Ted
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>

Wow!  You've certainly put some thought into this. Although you say you
disagree, I think we do agree. Saying a good standard should be followed
leaves room for additional functionality not specified in that standard.
I think the SQL drafters knew SQL would not cover all the functionality
of a good database system, but wanted to provide an abstraction for
features common to most database systems. As such most applications
using SQL would ideally be database agnostic.  A specialized application
is more likely to require RDMS specific features not included in the SQL
standard.  It's just sad that even selecting table names doesn't work
across databases.  It's not just Postgresql.  MySQL (last time I
checked) uses the filesystem name, which can be case sensitive or not
based on what OS is running.

Randall

This whole discussion is reminding me of one of my personal mantras, and
that is that relying on "artifacts" of behaviour is generally a bad
idea.

For instance, many databases accept != for not equal, but the sql
standard quite clearly says it's <>.

If you're relying on case folding meaning that you don't have to
consistently use the same capitalization when referring to variables,
table names, people, or anything else, you're asking for trouble down
the line, and for little or no real gain today.

I know that a lot of times we are stuck with some commercial package
that we can't do anything to fix, so I'm not aiming this comment at the
average dba, but at the developer.

Scott Marlowe wrote:
> This whole discussion is reminding me of one of my personal mantras, and
> that is that relying on "artifacts" of behaviour is generally a bad
> idea.
>
> For instance, many databases accept != for not equal, but the sql
> standard quite clearly says it's <>.
>
> If you're relying on case folding meaning that you don't have to
> consistently use the same capitalization when referring to variables,
> table names, people, or anything else, you're asking for trouble down
> the line, and for little or no real gain today.
>
> I know that a lot of times we are stuck with some commercial package
> that we can't do anything to fix, so I'm not aiming this comment at the
> average dba, but at the developer.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/
>

Yea, this is a commercial package, but it's actually doing it right.
Since it doesn't know how a user will name a table or column, it always
calls them as quoted strings in upper case which is standards compliant,
but doesn't work with PG.  So if a user names a table 55 and mine, it
calls "55 AND MINE" and for foo, it calls "FOO". Looks like they did it
right to me.

Randall

Randall Smith wrote:
> Scott Marlowe wrote:
> >This whole discussion is reminding me of one of my personal mantras, and
> >that is that relying on "artifacts" of behaviour is generally a bad
> >idea.
> >
> >For instance, many databases accept != for not equal, but the sql
> >standard quite clearly says it's <>.
> >
> >If you're relying on case folding meaning that you don't have to
> >consistently use the same capitalization when referring to variables,
> >table names, people, or anything else, you're asking for trouble down
> >the line, and for little or no real gain today.
> >
> >I know that a lot of times we are stuck with some commercial package
> >that we can't do anything to fix, so I'm not aiming this comment at the
> >average dba, but at the developer.
>
> Yea, this is a commercial package, but it's actually doing it right.
> Since it doesn't know how a user will name a table or column, it always
> calls them as quoted strings in upper case which is standards compliant,
> but doesn't work with PG.  So if a user names a table 55 and mine, it
> calls "55 AND MINE" and for foo, it calls "FOO". Looks like they did it
> right to me.

So what's the problem?  Just create the tables as all uppercase and you
should be fine, since the application must be systematic about quoting.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Wed, 14 Mar 2007, Randall Smith wrote:

> Scott Marlowe wrote:
> > This whole discussion is reminding me of one of my personal mantras, and
> > that is that relying on "artifacts" of behaviour is generally a bad
> > idea.
> >
> > For instance, many databases accept != for not equal, but the sql
> > standard quite clearly says it's <>.
> >
> > If you're relying on case folding meaning that you don't have to
> > consistently use the same capitalization when referring to variables,
> > table names, people, or anything else, you're asking for trouble down
> > the line, and for little or no real gain today.
> >
> > I know that a lot of times we are stuck with some commercial package
> > that we can't do anything to fix, so I'm not aiming this comment at the
> > average dba, but at the developer.
>
> Yea, this is a commercial package, but it's actually doing it right.
> Since it doesn't know how a user will name a table or column, it always
> calls them as quoted strings in upper case which is standards compliant,
> but doesn't work with PG.  So if a user names a table 55 and mine, it
> calls "55 AND MINE" and for foo, it calls "FOO". Looks like they did it
> right to me.

Maybe, but the 55 and mine example may or may not actually work. 55 and
mine isn't a valid regular identifier. "55 and mine" would be a valid
identifier, but that's not the same identifier as "55 AND MINE".

Alvaro Herrera wrote:
> Randall Smith wrote:
>> Scott Marlowe wrote:
>>> This whole discussion is reminding me of one of my personal mantras, and
>>> that is that relying on "artifacts" of behaviour is generally a bad
>>> idea.
>>>
>>> For instance, many databases accept != for not equal, but the sql
>>> standard quite clearly says it's <>.
>>>
>>> If you're relying on case folding meaning that you don't have to
>>> consistently use the same capitalization when referring to variables,
>>> table names, people, or anything else, you're asking for trouble down
>>> the line, and for little or no real gain today.
>>>
>>> I know that a lot of times we are stuck with some commercial package
>>> that we can't do anything to fix, so I'm not aiming this comment at the
>>> average dba, but at the developer.
>> Yea, this is a commercial package, but it's actually doing it right.
>> Since it doesn't know how a user will name a table or column, it always
>> calls them as quoted strings in upper case which is standards compliant,
>> but doesn't work with PG.  So if a user names a table 55 and mine, it
>> calls "55 AND MINE" and for foo, it calls "FOO". Looks like they did it
>> right to me.
>
> So what's the problem?  Just create the tables as all uppercase and you
> should be fine, since the application must be systematic about quoting.
>

The toolkit uses quoted identifiers and the application doesn't.  I have
a solution, which is to use views, but this thread is now about whether
or not Postgresql should and will support the current SQL standard,
which is to convert non-quoted identifiers to upper case.  Postgresql
converts them to lower case and that is the issue.  Since posting this
thread I've found this topic hashed out before in pgsql-hackers (thanks
Tom).

Randall

Stephan Szabo wrote:
> On Wed, 14 Mar 2007, Randall Smith wrote:
>
>> Scott Marlowe wrote:
>>> This whole discussion is reminding me of one of my personal mantras, and
>>> that is that relying on "artifacts" of behaviour is generally a bad
>>> idea.
>>>
>>> For instance, many databases accept != for not equal, but the sql
>>> standard quite clearly says it's <>.
>>>
>>> If you're relying on case folding meaning that you don't have to
>>> consistently use the same capitalization when referring to variables,
>>> table names, people, or anything else, you're asking for trouble down
>>> the line, and for little or no real gain today.
>>>
>>> I know that a lot of times we are stuck with some commercial package
>>> that we can't do anything to fix, so I'm not aiming this comment at the
>>> average dba, but at the developer.
>> Yea, this is a commercial package, but it's actually doing it right.
>> Since it doesn't know how a user will name a table or column, it always
>> calls them as quoted strings in upper case which is standards compliant,
>> but doesn't work with PG.  So if a user names a table 55 and mine, it
>> calls "55 AND MINE" and for foo, it calls "FOO". Looks like they did it
>> right to me.
>
> Maybe, but the 55 and mine example may or may not actually work. 55 and
> mine isn't a valid regular identifier. "55 and mine" would be a valid
> identifier, but that's not the same identifier as "55 AND MINE".
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

Your right. Its not a correct example.  I think the point is clear, though.

Randall

Randall Smith wrote:
>>> Are there plans to make Postgresql's behavior SQL compliant with
regards
>>> to quoted identifiers?  My specific need is to access tables named
in
>>> lower case by an uppercase quoted identifier, which is in line with
the
>>> SQL standard.
>>
>> You must not change the names of system tables, but you can
>> certainly create upper case views for them.
>
> That's my current solution.  I imagine this is not very
> efficient, but I could be wrong.

I think that the performance impact is negligible.
The pain is to maintain the views - the catalog tables can
change in every new release...

Yours,
Laurenz Albe

On Wed, 2007-03-14 at 17:33, Randall Smith wrote:
> Scott Marlowe wrote:
> > This whole discussion is reminding me of one of my personal mantras, and
> > that is that relying on "artifacts" of behaviour is generally a bad
> > idea.
> >
> > For instance, many databases accept != for not equal, but the sql
> > standard quite clearly says it's <>.
> >
> > If you're relying on case folding meaning that you don't have to
> > consistently use the same capitalization when referring to variables,
> > table names, people, or anything else, you're asking for trouble down
> > the line, and for little or no real gain today.
> >
> > I know that a lot of times we are stuck with some commercial package
> > that we can't do anything to fix, so I'm not aiming this comment at the
> > average dba, but at the developer.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> >                http://archives.postgresql.org/
> >
>
> Yea, this is a commercial package, but it's actually doing it right.
> Since it doesn't know how a user will name a table or column, it always
> calls them as quoted strings in upper case which is standards compliant,
> but doesn't work with PG.  So if a user names a table 55 and mine, it
> calls "55 AND MINE" and for foo, it calls "FOO". Looks like they did it
> right to me.

No they did it wrong.  I can make a table in oracle like this:

create table "Mine" ("MyName" varchar(200), "id" int)

and it won't work with your application, because it's broken.  Assuming
that everything is in upper case is just as bad as assuming it's in
lower case.  You can't assume the case because the user could be quoting
the name when he creates the table.

The rule, for Oracle, PostgreSQL, etc is that if you use quotes to name
it, you use quotes to access it.

With that philosophy, Oracle and PostgreSQL work just fine.

If you ignore that simple rule, you will get yourself into a corner with
either database.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

On Wed, 14 Mar 2007, Randall Smith wrote:

> Stephan Szabo wrote:
> > On Wed, 14 Mar 2007, Randall Smith wrote:
> >
> >> Scott Marlowe wrote:
> >>> This whole discussion is reminding me of one of my personal mantras, and
> >>> that is that relying on "artifacts" of behaviour is generally a bad
> >>> idea.
> >>>
> >>> For instance, many databases accept != for not equal, but the sql
> >>> standard quite clearly says it's <>.
> >>>
> >>> If you're relying on case folding meaning that you don't have to
> >>> consistently use the same capitalization when referring to variables,
> >>> table names, people, or anything else, you're asking for trouble down
> >>> the line, and for little or no real gain today.
> >>>
> >>> I know that a lot of times we are stuck with some commercial package
> >>> that we can't do anything to fix, so I'm not aiming this comment at the
> >>> average dba, but at the developer.
> >> Yea, this is a commercial package, but it's actually doing it right.
> >> Since it doesn't know how a user will name a table or column, it always
> >> calls them as quoted strings in upper case which is standards compliant,
> >> but doesn't work with PG.  So if a user names a table 55 and mine, it
> >> calls "55 AND MINE" and for foo, it calls "FOO". Looks like they did it
> >> right to me.
> >
> > Maybe, but the 55 and mine example may or may not actually work. 55 and
> > mine isn't a valid regular identifier. "55 and mine" would be a valid
> > identifier, but that's not the same identifier as "55 AND MINE".
> >
> Your right. Its not a correct example.  I think the point is clear, though.

Well, I was arguing about whether the app was doing it right. Assuming
that you can uppercase and put quotes around an arbitrary table name is
wrong too, because that's only valid for regular identifiers in SQL, so I
was wondering if it had support for things that were created as quoted
identifiers (which you might be able to use as a temporary workaround).
This isn't an argument against putting spec compliant behavior into
PostgreSQL, just more of a point that getting this right through the whole
system from app to db can be somewhat tricky even in the best case.

On Wed, 2007-03-14 at 17:33, Randall Smith wrote:
> Scott Marlowe wrote:
> > This whole discussion is reminding me of one of my personal mantras, and
> > that is that relying on "artifacts" of behaviour is generally a bad
> > idea.
> >
> > For instance, many databases accept != for not equal, but the sql
> > standard quite clearly says it's <>.
> >
> > If you're relying on case folding meaning that you don't have to
> > consistently use the same capitalization when referring to variables,
> > table names, people, or anything else, you're asking for trouble down
> > the line, and for little or no real gain today.
> >
> > I know that a lot of times we are stuck with some commercial package
> > that we can't do anything to fix, so I'm not aiming this comment at the
> > average dba, but at the developer.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> >                http://archives.postgresql.org/
> >
>
> Yea, this is a commercial package, but it's actually doing it right.
> Since it doesn't know how a user will name a table or column, it always
> calls them as quoted strings in upper case which is standards compliant,
> but doesn't work with PG.  So if a user names a table 55 and mine, it
> calls "55 AND MINE" and for foo, it calls "FOO". Looks like they did it
> right to me.

No they did it wrong.  I can make a table in oracle like this:

create table "Mine" ("MyName" varchar(200), "id" int)

and it won't work with your application, because it's broken.  Assuming
that everything is in upper case is just as bad as assuming it's in
lower case.  You can't assume the case because the user could be quoting
the name when he creates the table.

The rule, for Oracle, PostgreSQL, etc is that if you use quotes to name
it, you use quotes to access it.

With that philosophy, Oracle and PostgreSQL work just fine.

If you ignore that simple rule, you will get yourself into a corner with
either database.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

Randall Smith wrote:
> Thanks Tom.  I understand your points and I gather that the cost of
> making the change (even as an option) outweighs the benefits of SQL
> conformance for the developers.  Though I'm still of the same opinion.
>
> I'll give the pghackers forum a visit and since I'm already on the
> subject here, I'll make a direct comparison of the situation.
> Microsoft's Internet Explorer web browser is known to have poor support
> for the CSS standard, but refuses to fix it saying that it's too
> difficult and would break existing websites.  Many developers, myself
> included, prefer to code to the standard and have the html/css render
> correctly in all browsers.  In the long run, it's better to do it right
> even if that means breaking things today. If the standard is good (there
> are poor standards), there's no reason to not use the standard.

If we thought uppercase was a _better_ way to do things, we might some
day make the switch, but we don't, so the switch will probably never
happen.

--
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +