Thread: The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard
The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard
From
"David Delorme"
Date:
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. David Delorme http://dmdelorme.ca
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: > "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 >
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: > "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 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
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.