The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard

On Wed, 12 Mar 2003, David Delorme wrote:

I am trying to integrate MapInfo with a postgresql backend.

Problem:

MapInfo query uses both quoted and unquoted statements i.e.
"MAPINFO"."MAPINFO_MAPCATALOG" and mapinfo.mapinfo_mapcatalog

I can not change the query statements that MapInfo uses to talk to the odbc
driver.

I am in the process of changing to grass5 for all my map production.

I have a schema called MAPINFO and a table called MAPINFO_MAPCATALOG.

I need to fold unquoted names to uppercase.

I really only need to change this behaviour for this schema.

I'd write a program that acted as a gateway that answered odbc requests on
one side and forwarded them on to the database server on the other, and
vice versa. I.e. proxy the database server, and fold the names yourself
to all lower case no matter what the query says.

It's a hackish kluge, but not as big of one as what MapInfo would appear
to be :-)

On Wed, Mar 12, 2003 at 10:30:19AM -0700, scott.marlowe wrote:

It's a hackish kluge, but not as big of one as what MapInfo would appear
to be :-)

To be fair, the SQL spec requires that unquoted strings be folded to
upper case, so it's really PostgreSQL's violation of the spec that is
biting here.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

On Fri, 14 Mar 2003, Andrew Sullivan wrote:

On Wed, Mar 12, 2003 at 10:30:19AM -0700, scott.marlowe wrote:

It's a hackish kluge, but not as big of one as what MapInfo would appear
to be :-)

To be fair, the SQL spec requires that unquoted strings be folded to
upper case, so it's really PostgreSQL's violation of the spec that is
biting here.

True, very true. While mapinfo would be better had they picked one case /
quoting methodology and stuck to it, the lack of a fold_to_upper setting
or something similar in postgresql is a glaring flaw givin that folding to
lower is against spec, not just doing something a certain way because no
one bothered to define it.

Any chance a patch to set a GUC for case folding would get applied?

"scott.marlowe" <scott.marlowe@ihs.com> writes:

Any chance a patch to set a GUC for case folding would get applied?

Not really, because it would break everything in sight. (Hint: all the
system catalog names are lower-case ...)

regards, tom lane

On Fri, 14 Mar 2003, Tom Lane wrote:

"scott.marlowe" <scott.marlowe@ihs.com> writes:

Any chance a patch to set a GUC for case folding would get applied?

Not really, because it would break everything in sight. (Hint: all the
system catalog names are lower-case ...)

Right, we've mentioned it before. I still think it would be possible to
make one stick, as long as you either had some kind of kludgy wrapper for
system catalogs, or a way to make them uppercase after booting up.

I maintain that if postgresql works out of spec, it should, if possible,
have a way of working within the spec if it can, so we can attract more
conversion customers from the (currently) more comlpiant databases.

THAT explains why all that stuff is in UPPER case in ORACLE .... now I
see their dastardly plan!

Tom Lane wrote:

Do you have as much trouble reading those all-uppercase queries as I do?

---------------------------------------------------------------------------

Dennis Gearon wrote:

THAT explains why all that stuff is in UPPER case in ORACLE .... now I
see their dastardly plan!

Tom Lane wrote:

"scott.marlowe" <scott.marlowe@ihs.com> writes:

Any chance a patch to set a GUC for case folding would get applied?

Not really, because it would break everything in sight. (Hint: all the
system catalog names are lower-case ...)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

"scott.marlowe" <scott.marlowe@ihs.com> writes:

On Fri, 14 Mar 2003, Tom Lane wrote:

Not really, because it would break everything in sight. (Hint: all the
system catalog names are lower-case ...)

Right, we've mentioned it before. I still think it would be possible to
make one stick, as long as you either had some kind of kludgy wrapper for
system catalogs, or a way to make them uppercase after booting up.

I maintain that if postgresql works out of spec, it should, if possible,
have a way of working within the spec if it can, so we can attract more
conversion customers from the (currently) more comlpiant databases.

Yeah, I know. So far I've not seen any proposals for this that I could
support (in particular I don't want to be forced into looking at
upper-cased system catalogs) ... but it's an open problem. Maybe
someone will have a bright idea sometime about how to make everyone happy.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Do you have as much trouble reading those all-uppercase queries as I do?

Yeah, I hate 'em. It's well established that lowercase text is more
readable than uppercase. I think it's okay style to upcase a few
critical keywords like SELECT, FROM, WHERE, but beyond that give me
lowercase ...

But quite aside from questions of taste, we'd need an answer to the
backwards-compatibility problems before we could think about this.
Switching to the spec's notions about case would make autocommit look
like a nonissue :-(

regards, tom lane

On Fri, 14 Mar 2003, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Do you have as much trouble reading those all-uppercase queries as I do?

Yeah, I hate 'em. It's well established that lowercase text is more
readable than uppercase. I think it's okay style to upcase a few
critical keywords like SELECT, FROM, WHERE, but beyond that give me
lowercase ...

But quite aside from questions of taste, we'd need an answer to the
backwards-compatibility problems before we could think about this.
Switching to the spec's notions about case would make autocommit look
like a nonissue :-(

I'm strictly talking about a setting that would only be used on systems
being used to port from databases that fold to uppercase like Oracle. I
wouldn't want any changes that affected folks who didn't turn on the
option, only those who did.

For what I need, a hack that basically folded to upper everything that
didn't start with pg_ would work fine. Now that catalogs live in the
pg_catalog schema, I could just treat anything in the pg_catalog to be
fold to lower, while everything else would fold to upper.

Would that be an acceptable kludge, or would we want the catalog to be
folded to upper in those cases too? I can't see a reason for doing it
that way, since I doubt anyone porting Oracle sql code is gonna be messing
with the catalogs as well.

"scott.marlowe" <scott.marlowe@ihs.com> writes:

Now that catalogs live in the
pg_catalog schema, I could just treat anything in the pg_catalog to be
fold to lower, while everything else would fold to upper.

And you will determine whether something is in pg_catalog how, when you
haven't yet done a catalog lookup for it?

Keep in mind that the problem exists not only for system catalog names,
but for column names in those catalogs, not to mention built-in
functions. So looking at whether the name starts with "pg_" really
doesn't get you far as a way of deciding which way to fold.

Possibly you could make something of "fold to upper case originally,
but refold to lower case before looking in pg_catalog". I have no idea
how to implement that in a way that's not a horrid kluge though. Also,
it'd likely have unpleasant failure modes in some non-ASCII locales
where upcasing and downcasing aren't quite inverses. (Check the
archives for past problems with keywords in Turkish...)

regards, tom lane

About the only way it would work, is to have a conversion utility, that
walks a user through all the questionable areas. Sounds like windbloze,
only sideways, huh ;-)

Tom Lane wrote:

At 01:50 PM 3/14/03 -0500, Andrew Sullivan wrote:

On Wed, Mar 12, 2003 at 10:30:19AM -0700, scott.marlowe wrote:

It's a hackish kluge, but not as big of one as what MapInfo would appear
to be :-)

To be fair, the SQL spec requires that unquoted strings be folded to
upper case, so it's really PostgreSQL's violation of the spec that is
biting here.

Yah, but following the SQL spec in that would violate my eyes :).

Would a compile time setting be easier to do than a GUC? Everything
unquoted folds up, and the system tables when doing initdb are all upper
case (UGH!).

That'll be fun to regression test tho ;).

Link.

Hi All,

I've lot of date fields in my postgres tables. When ever I run a query on
date fields, the date is retrieved in
"yyyy-mm-dd" format. Every time, I've to use to_char function to convert
those fields into string. Is there is any server setting to change the
default date retrieval format into dd/mm/yyyy? Please let me know, if there
is any way. It will be really helpful for me.

Thanks & Regards
Jaisankar

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:

Would a compile time setting be easier to do than a GUC? Everything
unquoted folds up, and the system tables when doing initdb are all upper
case (UGH!).

This might be doable as far as the backend itself goes, but I worry
about the repercussions for client-side code.  To have a usable
installation you'd need libpq, psql, pg_dump, JDBC driver, etc etc
to all work in either mode.  Not sure how far the implications extend
--- but our experience with the autocommit option leads me to be wary.

You could possibly get away with making pg_dump depend on the same
compile-time option as the backend, but for the client libraries in
general it would be a highly annoying restriction to have to be sure
all your machines are compiled the same way. libpq's
internally-generated queries are few and simple enough that we'd not
have much problem making sure they are case-agnostic, but can the same
be said of JDBC or ODBC?

regards, tom lane

On Sat, 2003-03-15 at 06:38, Arunachalam Jaisankar wrote:

Hi All,

It's not a good idea to reply to a message with a completely unrelated
subject; start a new thread.

I've lot of date fields in my postgres tables. When ever I run a query on
date fields, the date is retrieved in
"yyyy-mm-dd" format. Every time, I've to use to_char function to convert
those fields into string. Is there is any server setting to change the
default date retrieval format into dd/mm/yyyy? Please let me know, if there
is any way. It will be really helpful for me.

SET DATESYTLE TO SQL,European;

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Blessed are the poor in spirit, for theirs is the
kingdom of heaven...Blessed are they which do hunger
and thirst after righteousness, for they shall be
filled...Blessed are the pure in heart, for they shall
see God." Matthew 5:3,6,8

On Fri, Mar 14, 2003 at 02:08:54PM -0700, scott.marlowe wrote:

I'm strictly talking about a setting that would only be used on systems
being used to port from databases that fold to uppercase like Oracle. I
wouldn't want any changes that affected folks who didn't turn on the
option, only those who did.

Given that kludges are the order of the day, what about creating an
updatable vire for every (candidate) table, with upper-case names
(for, I suppose, the tables and the fields)? It's not pretty, but
it'd get you there. I think it wouldn't be too hard to write a
script to do this automatically.

It'd make an unholy mess, of course, but nobody said this had to be
clean.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

On Fri, 14 Mar 2003, Tom Lane wrote:

"scott.marlowe" <scott.marlowe@ihs.com> writes:

Now that catalogs live in the
pg_catalog schema, I could just treat anything in the pg_catalog to be
fold to lower, while everything else would fold to upper.

And you will determine whether something is in pg_catalog how, when you
haven't yet done a catalog lookup for it?

Keep in mind that the problem exists not only for system catalog names,
but for column names in those catalogs, not to mention built-in
functions. So looking at whether the name starts with "pg_" really
doesn't get you far as a way of deciding which way to fold.

Possibly you could make something of "fold to upper case originally,
but refold to lower case before looking in pg_catalog". I have no idea
how to implement that in a way that's not a horrid kluge though. Also,
it'd likely have unpleasant failure modes in some non-ASCII locales
where upcasing and downcasing aren't quite inverses. (Check the
archives for past problems with keywords in Turkish...)

Agreed, it's a horrible kludge that way. Is it possible to make it fold
everything to upper case, then do some kind of import/export of the system
catalog to match? Or are parts of the catalog case sensitive, demanding
lower case?

For what we wanna do with it, a postgresql that folds everything to upper
is just fine.