Re: Referencing created tables fails with message that

On Mon, Feb 28, 2005 at 12:50:25PM +0100, Tommy Svensson wrote:

> - I've used Oracle, DB2, Mimer, and HSQLDB before, and my experience
> with these led me
> to beleive that SQL was case insensitive. In fact, I was so sure of it
> that a case problem
> just never occured to me.

Case isn't a problem if you don't quote identifiers because unquoted
identifiers will be folded to lower case, both when you create them
and then later when you reference them. For example, if you create
a table with this command:

CREATE TABLE XYZ (I INTEGER);

then the system folds XYZ and I to lower case:

\dt
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+-------
public | xyz | table | mfuhr

\d xyz
Table "public.xyz"
Column | Type | Modifiers
--------+---------+-----------
i | integer |

The following queries should all work (not an all-inclusive list):

SELECT I FROM XYZ;
SELECT i FROM xyz;
SELECT I FROM Xyz;
select i from xyz;
sEleCt i fRoM xYz;

But if you quote identifiers when you create them, then they'll be
created with the exact case you specified and you'll need to quote
them whenever you use them:

CREATE TABLE "XYZ" ("I" INTEGER);

\dt
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+-------
public | XYZ | table | mfuhr

\d "XYZ"
Table "public.XYZ"
Column | Type | Modifiers
--------+---------+-----------
I | integer |

SELECT "I" FROM "XYZ"; -- works
SELECT I FROM XYZ; -- fails

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/