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
interoperabilit y 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.o rg devrim.gunduz~l inux.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 YourEmailAddres sHere" to ma*******@postg resql.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*******@postg resql.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 interoperabilit y 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*******@postg resql.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*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Harald Fuchs]

In article <20************ ************@gm x.net>,
Peter Eisentraut <pe*****@gmx.ne t> 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 YourEmailAddres sHere" to ma*******@postg resql.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