Thread: PG case sensitivity
Hello, I am running into a problem with PGs case sensitivity with regard to column and table names. I am using program components that require the object names returned from database metadata queries to be in uppercase. Therefore, I am forced to use double quotes in the table creation scripts, like create table "BLA" (); However, after doing that, all scripts that reference objects without quotes fail, as PG seems to internally translate to lowercase in the absence of quotes. I am forced to touch each and every column and table name in every script. Questions: 1) can PG be configured to operate case insensitive? 2) why in the world was case sensitivity introduced at all? AFAIK, the SQL standard explicitly states that names are case insensitive, and it seems to me that PG goes against that standard. In fact, if there is no solution to this problem, my conclusion will probably be to drop PG altogether, as we need DB interoperability on the program and script level, and this is becoming unmaintainable.. thanks, christian
Hi,
On Tue, 14 Sep 2004, Christian Sell wrote:
> I am running into a problem with PGs case sensitivity with regard to column and
> table names. I am using program components that require the object names
> returned from database metadata queries to be in uppercase. Therefore, I am
> forced to use double quotes in the table creation scripts, like
>
> create table "BLA" ();
<snip>
I'll not answer your question but... I wonder why people want to use
capital letters in table names... I've been developing apps for years and
I never ever needed to capitalize my table names...
Regards,
--
Devrim GUNDUZ
devrim~gunduz.org devrim.gunduz~linux.org.tr
http://www.tdmsoft.com
http://www.gunduz.org
On Tue, 2004-09-14 at 12:37, Christian Sell wrote: > Hello, > > I am running into a problem with PGs case sensitivity with regard to column and > table names. I am using program components that require the object names > returned from database metadata queries to be in uppercase. Therefore, I am > forced to use double quotes in the table creation scripts, like Why do programs that interface with case-insensitive SQL require a particular case? > create table "BLA" (); > > However, after doing that, all scripts that reference objects without quotes > fail, as PG seems to internally translate to lowercase in the absence of > quotes. I am forced to touch each and every column and table name in every > script. Questions: > > 1) can PG be configured to operate case insensitive? It does, but in lower case (which is a good deal more readable). Using lower rather than upper case is a decision made many years ago. > 2) why in the world was case sensitivity introduced at all? AFAIK, the SQL > standard explicitly states that names are case insensitive, and it seems to me > that PG goes against that standard. It doesn't. It simply folds to lower case rather than upper case. If things are truly case-insensitive, this should be of no consequence. The fault is with your program components that are insisting on upper case rather than accepting either case. Perhaps you need some intermediate component to swap case on identifiers for their benefit. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== "But without faith it is impossible to please him; for he that cometh to God must believe that he is, and that he is a rewarder of them that diligently seek him." Hebrews 11:6
Christian Sell wrote: > 1) can PG be configured to operate case insensitive? No. > 2) why in the world was case sensitivity introduced at all? Because the SQL standard specifies it. -- Peter Eisentraut http://developer.postgresql.org/~petere/
I have a table containing coordinates and I want to insert these into
another table, converting them to boxes.
I would like to use the same syntax as arrays :
INSERT INTO ... (coords) SELECT ARRAY[a,b,c] FROM ...
But I want boxes. Is there a way to do this ?
SELECT BOX[[a,b],[c,d]] FROM ...
raises a Syntax Error.
And I'd like to avoid to concatenate strings to build a box
representation like
'((1,2),(3,4))'::box;
Is there a way ?
Thanks !
> I'll not answer your question but... I wonder why people want to use > capital letters in table names... I've been developing apps for years and > I never ever needed to capitalize my table names... as I wrote in my mail, its a matter of interoperability in a multi-DBMS environment with existing scripts. If it was all PG, no problem. Anyhow, I still dont see why select * from A -- and select * from "A" are different things, if the standard requires case insensitivity. christian
Christian Sell wrote: > Anyhow, I still dont see why > > select * from A > -- and > select * from "A" > > are different things, if the standard requires case insensitivity. You're operating under flawed assumptions. Please read the documentation for the full details: http://www.postgresql.org/docs/7.4/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS -- Peter Eisentraut http://developer.postgresql.org/~petere/
In article <200409141353.12147.peter_e@gmx.net>, Peter Eisentraut <peter_e@gmx.net> writes: > Christian Sell wrote: >> 1) can PG be configured to operate case insensitive? > No. >> 2) why in the world was case sensitivity introduced at all? > Because the SQL standard specifies it. I think it's the other way round. "CREATE TABLE SHIT" and "create table shit" are the same according to the SQL standard, and they are the same for PostgreSQL. The 'gotcha' is that when you ask PostgreSQL for the table name you get "shit" and not "SHIT", as opposed to many other DBMSs.
On Tue, 14 Sep 2004, Christian Sell wrote: > Hello, > > I am running into a problem with PGs case sensitivity with regard to column and > table names. I am using program components that require the object names > returned from database metadata queries to be in uppercase. Therefore, I am > forced to use double quotes in the table creation scripts, like > > create table "BLA" (); > > However, after doing that, all scripts that reference objects without quotes > fail, as PG seems to internally translate to lowercase in the absence of > quotes. I am forced to touch each and every column and table name in every > script. Questions: > > 1) can PG be configured to operate case insensitive? > 2) why in the world was case sensitivity introduced at all? AFAIK, the SQL > standard explicitly states that names are case insensitive, and it seems to me > that PG goes against that standard. In fact, if there is no solution to this No, the SQL spec says that names are case folded to uppercase, although we currently case-fold to lowercase (and can't really wholesale change that for backwards compatibility reasons). There's been talk about supporting a mode which case folds the other direction. In general, however, mixing quoted and unquoted names is dangerous in all complient databases, because in none would "Bla" and bla or BLA be the same name.
On Tue, 14 Sep 2004, Oliver Elphick wrote: > The fault is with your program components that are insisting on upper > case rather than accepting either case. In defence of this unknown component, the sql specifications says that identifiers should be upper cased where pg do lower case. I would welcome a initdb setting that defines what to use. -- /Dennis Björklund
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> ... There's been talk about supporting a
> mode which case folds the other direction. In general, however, mixing
> quoted and unquoted names is dangerous in all complient databases, because
> in none would "Bla" and bla or BLA be the same name.
We have looked at this before and keep coming up with the conclusion
that (a) on-the-fly changes in the backend behavior are impractical;
and (b) if we only get to have one behavior, the current one is the best
compromise we are going to get.
I wonder though whether we could offer some tweaking on client side.
For instance consider the JDBC driver's getMetaData operations.
You could imagine a JDBC driver mode that would smash all returned
identifiers to upper case. For an application that was willing to
promise it would never explicitly quote identifiers in the SQL it
generates, this would not break anything AFAICS; and it would satisfy
app code that was expecting to see upper instead of lower case.
regards, tom lane
On Tue, 14 Sep 2004, Tom Lane wrote: > Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > > ... There's been talk about supporting a > > mode which case folds the other direction. In general, however, mixing > > quoted and unquoted names is dangerous in all complient databases, because > > in none would "Bla" and bla or BLA be the same name. > > We have looked at this before and keep coming up with the conclusion > that (a) on-the-fly changes in the backend behavior are impractical; > and (b) if we only get to have one behavior, the current one is the best > compromise we are going to get. An initdb-time flag might be okay, except that I don't know if any of the applications (pg_dump, etc) would need to know about it and it would take some work to get the database to initialize correctly in the first place. > I wonder though whether we could offer some tweaking on client side. > For instance consider the JDBC driver's getMetaData operations. > You could imagine a JDBC driver mode that would smash all returned > identifiers to upper case. For an application that was willing to > promise it would never explicitly quote identifiers in the SQL it > generates, this would not break anything AFAICS; and it would satisfy > app code that was expecting to see upper instead of lower case. That's true, although it seems like in many of these cases the application is also using quoting inconsistently.
Tom Lane wrote: > I wonder though whether we could offer some tweaking on client side. > For instance consider the JDBC driver's getMetaData operations. > You could imagine a JDBC driver mode that would smash all returned > identifiers to upper case. Then again, JDBC applications can query DatabaseMetaData.storesUpperCaseIdentifiers(). -- Peter Eisentraut http://developer.postgresql.org/~petere/
Please don't reply to existing threads to start a new thread. This makes the archives less usable. On Tue, Sep 14, 2004 at 14:00:43 +0200, Pierre-Frédéric Caillaud <lists@boutiquenumerique.com> wrote: > > I have a table containing coordinates and I want to insert these > into another table, converting them to boxes. > > I would like to use the same syntax as arrays : > > INSERT INTO ... (coords) SELECT ARRAY[a,b,c] FROM ... > > But I want boxes. Is there a way to do this ? > > SELECT BOX[[a,b],[c,d]] FROM ... You want something like box(point(a,b),point(c,d)) . > raises a Syntax Error. > And I'd like to avoid to concatenate strings to build a box > representation like > '((1,2),(3,4))'::box; > > Is there a way ? > > Thanks ! > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings
> > The fault is with your program components that are insisting on upper > > case rather than accepting either case. > > In defence of this unknown component, the sql specifications says that > identifiers should be upper cased where pg do lower case. > > I would welcome a initdb setting that defines what to use. yes, that would do it. Sounds far better than having the JDBC driver handle this. Christian Sell