PG case sensitivity

[Christian Sell]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Devrim GUNDUZ]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Oliver Elphick]

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 ol**@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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Peter Eisentraut]

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/
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Pierre-Frédéric Caillaud]

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 !

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Christian Sell]

> 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

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

[Peter Eisentraut]

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/s...AX-IDENTIFIERS

--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Harald Fuchs]

In article <20************************@gmx.net>,
Peter Eisentraut <pe*****@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.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Stephan Szabo]

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Dennis Bjorklund]

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Stephan Szabo <ss****@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

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

[Stephan Szabo]

On Tue, 14 Sep 2004, Tom Lane wrote:

Stephan Szabo <ss****@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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Peter Eisentraut]

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/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Bruno Wolff III]

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 <li***@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Christian Sell]

> > 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

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

http://archives.postgresql.org