Tom Lane [] wrote:
>  Quite aside from the impact on the server (which would be extensive),
it would break every nontrivial application, and force them all to try
to deal with each possible folding behavior.
I have read through the various threads related to this issue that you
supplied. And, it looks quite clear that a change is unlikely. Maybe it
is even a bad idea, though personally I think it could prove worth the
pain that you obviously anticipate.

However, before fully dropping this issue, I do have one comment I would
like to make (just for the record) on your statement that a change in
the current behavior would break most non-trivial applications.

I assume you are talking about general purpose tools that attempt to
interact with any database in any configuration. Obviously, a purpose
built tool, such as our own internal database applications, would be
designed only for the behavior of the databases it is intended to work

I have, over the past few months, tried quite a large number (10, maybe
a few more than that) of general purpose tools against PostgreSQL
looking for replacements for some of the tools we use against our old
database server. And, almost none of them work well if I quote
identifiers on the server. Almost all work perfectly well if I accept
that all my symbols will be converted to lower case and do not quote the
identifiers. Most fail - often not even in corner cases - when the
catalog (properly) returns a mixed case symbol like WeeklySales. Very
few of the tools I have tried seem to know to quote such a symbol to
preserve the case when generating a query to send back to the server. 

Most of the tools I have tested work through ODBC, though a few connect
directly to PostgreSQL. Both types of tools have exhibited similar
issues. Maybe the ODBC connection is relevant, since it appears that
Microsoft SQL Server does what our current server does and preserves
case, at least in some modes. A good fraction of the tools I have tried,
push their compatibility with SQL Server.

So, the current behavior already breaks many tools unless one accepts
that all symbols on the server are lower case. At root, based on reading
the threads you provided, this probably indicates defects in the tools,
rather than a problem with PostgreSQL. My reading of the standard text
quoted in various places is that any mixed case identifier returned from
the catalog has to be quoted to match in a query (whether you fold to
lower or upper case). 

But, I can easily imagine a good number of people deciding they want
mixed case on the server, and so quoting their identifiers. And, then
deciding PostgreSQL is defective, rather than deciding their favorite
administration or query tool is defective. Almost all of the tools I
tried worked fine when I had all lower case symbols on the server. Based
on observing the generated SQL, most of the tools that failed for me
when I had mixed case symbols on the server would work against a case
preserving mode in PostgreSQL. The tools generally pass through the
catalog reported symbols without manipulation. 

I fully understand your concern to keep the number of modal behaviors to
a minimum. And, the upper case folding looks to have a lot of side
effects, and so is a whole lot harder to implement than I expected.
Preserving case, which is what we actually want, by contrast still looks
pretty easy, though it is clearly not standard compliant. 

I do not have any more to add on the subject. I just wanted to make this
note that a good fraction of third party tools already fail unless one
never quotes identifiers on PostgreSQL. 

Thank you for taking the time to reply to my original inquiry. 

Ian Lewis

Sent via pgsql-hackers mailing list (
To make changes to your subscription:

Reply via email to