Thread: case sensitivity?

setup - pgsql 7.1.2 cygwin binary distribution

i first noticed this when running psql - whenever i
perform a select all of the columns are returned as
all lowercase. that wasn't too disturbing, i just
found it to be slightly odd.

what was disturbing was when the same thing happened
going through the odbc driver on win32 and all of my
old perl code that worked under microsoft stopped
working under postgres because i was looking for
keys in the hash retrieved by the $dsn->dataHash()
by the MixedCase names that i used to create the
tables with in the sql.

that meant that i had to run through my code to
modify all of the hash references to all lowercase.
needless to say, that was a pain.

is there any setting that will allow postgres to
keep mixed case column names when creating a db?

rjsjr

On Sun, 26 Aug 2001 16:06:14 -0500
"Robert J. Sanford, Jr." <rsanford@nolimitsystems.com> wrote:

> is there any setting that will allow postgres to
> keep mixed case column names when creating a db?

yes:
when you create table make sure all identifiers are inside quotation marks
("). like this:
create table "MyTable" (
    id SERIAL,
    "Name" TEXT,
    "Phone" TEXT,
    PRIMARY KEY (id)
);

but since then all your select's have to work with quotation mark, i.e. this
will not work:
select name from mytable;
nor will work this:
select Name from MyTable;
you have to explicity do:
select "Name" from "MyTable";

the reason is very simple. pgsql internally converts all names, identifiers
and so on to lower case. both in create statements and in select's, update's
and so on.
i hope you have it clear now.

depesz

p.s. sorry for my english

--
hubert depesz lubaczewski                          http://www.depesz.pl/
------------------------------------------------------------------------
... and the end of all our exploring  will be to arrive where we started
and know the place for the first time.                    -- T. S. Eliot