Thread: PG case sensitivity

PG case sensitivity

From
Christian Sell
Date:
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

Re: PG case sensitivity

From
Devrim GUNDUZ
Date:
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

Re: PG case sensitivity

From
Oliver Elphick
Date:
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


Re: PG case sensitivity

From
Peter Eisentraut
Date:
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/


Boxes

From
Pierre-Frédéric Caillaud
Date:
    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 !

Re: PG case sensitivity

From
Christian Sell
Date:
> 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

Re: PG case sensitivity

From
Peter Eisentraut
Date:
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/


Re: PG case sensitivity

From
Harald Fuchs
Date:
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.

Re: PG case sensitivity

From
Stephan Szabo
Date:
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.

Re: PG case sensitivity

From
Dennis Bjorklund
Date:
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


Re: PG case sensitivity

From
Tom Lane
Date:
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

Re: PG case sensitivity

From
Stephan Szabo
Date:
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.

Re: PG case sensitivity

From
Peter Eisentraut
Date:
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/


Re: Boxes

From
Bruno Wolff III
Date:
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

Re: PG case sensitivity

From
Christian Sell
Date:
> > 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