Thread: Database Name Case Sensitivity
Maybe this isn't technically a bug, but I found it strange. With PostgreSQL 7.0.3 and 7.1beta6, I can create a database "FOO", but can only later connect to it as "foo". I'm not sure if this is a psql thing or a backend thing, or if it's intended to be this way, but it makes some of my scripts more complicated. -- Shaw Terwilliger <sterwill@sourcegear.com> SourceGear Corporation 217.356.0105 x 641
I just ran into that too, and find it most undesirable. I don't know whether that is part of the SQL spec or not, but it seems very odd. The queries seem to be converted to lowercase before they ever reach the SQL engine. Brian +++++++++++++++++++++++++++++ Flash web sites from $149/year w/ hosting http://www.eflashcash.com/er/websuccess/ +++++++++++++++++++++++++++++ > Maybe this isn't technically a bug, but I found it strange. With PostgreSQL > 7.0.3 and 7.1beta6, I can create a database "FOO", but can only later connect to > it as "foo". I'm not sure if this is a psql thing or a backend thing, or if > it's intended to be this way, but it makes some of my scripts more complicated. > > -- > Shaw Terwilliger <sterwill@sourcegear.com> > SourceGear Corporation > 217.356.0105 x 641 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Thu, 5 Apr 2001, Brian T. Allen wrote: > I just ran into that too, and find it most undesirable. I don't know > whether that is part of the SQL spec or not, but it seems very odd. The > queries seem to be converted to lowercase before they ever reach the SQL > engine. Everything is postgresql is lowercased, unless system identifiers (double quotes) are wrapped around it. CREATE DATABASE foo is the same as CREATE DATABASE FOO or CREATE DATABASE Foo You must connect to it with \c foo However, if you CREATE TABLE "FOO" then it is not lowercased. You must connect as \c FOO I think this is a Good Thing. It allows most people to have case-insensitive system identifiers (at least they appear case-insensitive, since it lowercases everything). This plays well with other database systems. If you want case-sensitive identifiers, you just wrap them wiuth the double quotes. (cf to the mess in MySQL, where some things are case-sensitive, and some things aren't, and it varies based on whether the server OS is case sensitive. Ick.) -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
I'm not sure about database names but table and field names can be upper case (or contain upper case characters) you just have to reference the table/field in double quotes.. Example : SELECT * FROM "FoBaR"; Hope that helps. -Mitch Software development : You can have it cheap, fast or working. Choose two. ----- Original Message ----- From: "Brian T. Allen" <brian@gzmarketing.com> To: "Shaw Terwilliger" <sterwill@sourcegear.com>; <pgsql-general@postgresql.org> Sent: Thursday, April 05, 2001 3:53 PM Subject: Re: Database Name Case Sensitivity > I just ran into that too, and find it most undesirable. I don't know > whether that is part of the SQL spec or not, but it seems very odd. The > queries seem to be converted to lowercase before they ever reach the SQL > engine. > > Brian > +++++++++++++++++++++++++++++ > Flash web sites from $149/year w/ hosting > http://www.eflashcash.com/er/websuccess/ > +++++++++++++++++++++++++++++ > > > > Maybe this isn't technically a bug, but I found it strange. With > PostgreSQL > > 7.0.3 and 7.1beta6, I can create a database "FOO", but can only later > connect to > > it as "foo". I'm not sure if this is a psql thing or a backend thing, or > if > > it's intended to be this way, but it makes some of my scripts more > complicated. > > > > -- > > Shaw Terwilliger <sterwill@sourcegear.com> > > SourceGear Corporation > > 217.356.0105 x 641 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
I am sure it is a Good Thing, thank you for the explanation. I will just have to change my thinking to all lowercase and get used to it. Sort of on the same topic, does anyone know of a PostgreSQL primer for those making the change from MySQL. Changing religions :} is never easy, something to ease the transition and point out the differences and gotchas would be very helpful. Thanks for the help, Brian +++++++++++++++++++++++++++++ Flash web sites from $149/year w/ hosting http://www.eflashcash.com/er/websuccess/ +++++++++++++++++++++++++++++ > On Thu, 5 Apr 2001, Brian T. Allen wrote: > > > I just ran into that too, and find it most undesirable. I don't know > > whether that is part of the SQL spec or not, but it seems very odd. The > > queries seem to be converted to lowercase before they ever reach the SQL > > engine. > > Everything is postgresql is lowercased, unless system identifiers (double > quotes) are wrapped around it. > > CREATE DATABASE foo > > is the same as > > CREATE DATABASE FOO > > or > > CREATE DATABASE Foo > > > You must connect to it with > > \c foo > > > However, if you > > CREATE TABLE "FOO" > > then it is not lowercased. You must connect as > > \c FOO > > > I think this is a Good Thing. It allows most people to have > case-insensitive system identifiers (at least they appear > case-insensitive, since it lowercases everything). This plays well with > other database systems. If you want case-sensitive identifiers, you just > wrap them wiuth the double quotes. > > (cf to the mess in MySQL, where some things are case-sensitive, and some > things aren't, and it varies based on whether the server OS is case > sensitive. Ick.) > > -- > Joel Burton <jburton@scw.org> > Director of Information Systems, Support Center of Washington > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Brian T. Allen wrote:
> I just ran into that too, and find it most undesirable. I don't know
> whether that is part of the SQL spec or not, but it seems very odd. The
> queries seem to be converted to lowercase before they ever reach the SQL
> engine.
Correct me if I'm wrong, but doesn't it look like a trend in PostgreSQL
development? Instead of adding options and keeping the standard or
previous behaviour, a new behaviour is attached to old syntax, and if
you want to keep the standard or old results you have to do something else.
The "Right Thing"(TM) to do in this case would be to be case insensitive
by default, according to the traditional (if not standard) SQL practice,
and do case sensitiveness if using quotes.
Just my 2c.
>> Maybe this isn't technically a bug, but I found it strange. With
> PostgreSQL
>> 7.0.3 and 7.1beta6, I can create a database "FOO", but can only later
> connect to
>> it as "foo". I'm not sure if this is a psql thing or a backend thing, or
> if
>> it's intended to be this way, but it makes some of my scripts more
> complicated.
>
>> --
>> Shaw Terwilliger <sterwill@sourcegear.com>
--
_
/ \ Leandro Guimarães Faria Corsetti Dutra +55 (11) 3040 8913
\ / Amdocs at Bell Canada +1 (514) 786 87 47
X Support Center, São Paulo, Brazil mailto:adbaamd@bell.ca
/ \ http://terravista.pt./Enseada/1989/ mailto:leandrod@amdocs.com
On Thu, 5 Apr 2001, Brian T. Allen wrote:
> I am sure it is a Good Thing, thank you for the explanation. I will just
> have to change my thinking to all lowercase and get used to it.
>
> Sort of on the same topic, does anyone know of a PostgreSQL primer for those
> making the change from MySQL. Changing religions :} is never easy,
> something to ease the transition and point out the differences and gotchas
> would be very helpful.
Let me not add to any flame wars here, please.
MySQL might be useful for the most complicated database project in the
universe, and might be scalable to counting the grains on sand on every
beach. However--it's support for many 'high-end' database features is
scarce (to it's credit, in its place is an extremely easy-to-install,
easy-to-administer, relatively speedy and painless database that works
great only practically every OS you'd care to use.)
I don't have a simple list of 'gotchas' -- instead, I wanted to answer the
other question. What are the non-gotchas, non-simple differences between
the two.
[Actually I have some gotchas:]
- MySQL uses nonstandard '#' as a comment line. Pg doesn't. Use '--', both
understand it
- MySQL uses ' or " to quote values, as in WHERE name = "John". Pg uses
only single quotes for this, double quotes are used to quote system
identifiers (field name, table names, etc.)
- The whole case-sensitivity issue, above.
- Pg and MySQL seem to differ most in handling of dates, and the names of
functions that handle dates.
But (for me, at least) are more than
just 'how do I do this MySQL thing in PostgreSQL', but 'is there a much
better way to think about this, which MySQL never even supported.'
For example:
Imagine you're building a dynamic web for human resources. You want to
list every current senior staff member's name, some info about them, and a
list of their
goals.
With MySQL, you'd do something like this:
(this is generic pseudo-code, it would easily translate to PHP, Zope,
EmbPerl, etc.)
<in sql="SELECT staffid, firstname, lastname FROM Staff
WHERE datefired ISNULL and seniorstaff = TRUE">
<h1>$firstname $lastname</h1>
<ul>
<in sql="SELECT goalinfo FROM Goals WHERE staffid=$staffid">
<li>$goalinfo
</in>
</ul>
</in>
That's great, and it works fine. You can easily translate this to
PostgreSQL.
Would you want to, though? PostgreSQL has many features MySQL doesn't,
like:
* views
* procedural languages
* triggers
* customizable aggregates
* transactions
For instance, rather than coding in the web front end the logic of
is-not-fired and is-senior-staff, in PostgreSQL, I'd make a VIEW of all
staff for which we want to show goals:
CREATE VIEW staff_having_goals AS
SELECT staffid, firstname || lastname as fullname
FROM Staff
WHERE datefired ISNULL and seniorstaff = TRUE
ORDER BY lastname, firstname
Now, my web programming doesn't have to worry about the lower level
concerns. Imagine if this same list of people and goals appeared dozens of
times on your site--I've moved from having this scattered in many places,
to having it encapsulated in one place.
PostgreSQL also allows procedural languages (perl, tcl, python [alpha],
and an Oracle-alike, plpgsql). These allow you to create functions in your
database (and even non-sysadmins can use them, as the functions fit in the
PostgreSQL security model).
[ Yes, MySQL has user functions, which last time I checked, had to be
written in C, and linked into the database. A nice feature, to be sure,
but VERY different from having high-level procedural languages usable w/o
root privileges! ]
We might use these procedural languages to create lists, handle database
events (if a record is added here, automatically track this here,
etc.) (You might have a function to calculate a staff member's hourly
compensation from their salary, which, IMHO, *should* be a database
function, not a function coded for every different web project or
front-end project you had.)
PostgreSQL also has transactions, which can remove some of the hairy
if-an-error-happened-back-out-all-database-work code. (MySQL, to its
credit, has transactions in their new MaxSQL thingie.)
So:
The things that are handled differently are fairly small.
The real lesson is to learn about what features PostgreSQL has an figure
out *why* to use them!
I'd start w/the five about (views, procedural languages, triggers,
customizable aggregates, transactions) and make sure that you understand
exactly what they are, how to use them, and how wonderful they are.
I hope this helps, and I do hope it doesn't sound condescending. I moved
to PostgreSQL from using MySQL, and for several months after first playing
with it, I just thought it was a bigger, more complicated database that
did 'the same stuff' as MySQL. It took me a while to really realize how
great the 'other' features are.
Good luck!
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington
On Thu, 5 Apr 2001, ADBAAMD wrote: > Brian T. Allen wrote: > > > I just ran into that too, and find it most undesirable. I don't know > > whether that is part of the SQL spec or not, but it seems very odd. The > > queries seem to be converted to lowercase before they ever reach the SQL > > engine. > > Correct me if I'm wrong, but doesn't it look like a trend in PostgreSQL > development? Instead of adding options and keeping the standard or > previous behaviour, a new behaviour is attached to old syntax, and if > you want to keep the standard or old results you have to do something else. Hmmm. I'm not sure that's a charitable reading of the fantastic work of the Global Development Group. They seem *quite* sensitive to not breaking existing stuff. (In fact, for people like me, who work in small IT departments, and love to rebuild systems, sometimes they seem *too* concerned with not breaking old stuff! ;-) ) > The "Right Thing"(TM) to do in this case would be to be case insensitive > by default, according to the traditional (if not standard) SQL practice, > and do case sensitiveness if using quotes. PostgreSQL *does* do what you'd expect. It is 'case-insensitive-appearing'. AFAIK, it's only when using the \c dbname in psql that you have different behavior (that it cares if you specify in in the correct case.) Perhaps more consistent would be \c dbname is converted to lowercase \c "DBname" is treated, case insensitive (which is how everything else in the PG world works.) But, to substitue this now would break existing apps, wouldn't it? ;-) -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
ADBAAMD writes: > Brian T. Allen wrote: > > > I just ran into that too, and find it most undesirable. I don't know > > whether that is part of the SQL spec or not, but it seems very odd. The > > queries seem to be converted to lowercase before they ever reach the SQL > > engine. > > Correct me if I'm wrong, but doesn't it look like a trend in PostgreSQL > development? Instead of adding options and keeping the standard or > previous behaviour, a new behaviour is attached to old syntax, and if > you want to keep the standard or old results you have to do something else. > > The "Right Thing"(TM) to do in this case would be to be case insensitive > by default, according to the traditional (if not standard) SQL practice, > and do case sensitiveness if using quotes. The standard behaviour, which has been in PostgreSQL for the longest time, is that unquoted identifiers are folded to lower case (actually it's supposed to be upper case in SQL, but that won't make a difference here), whereas quoted identifiers preserve case. There is no "previous" or traditional alternative here. This rule is only valid in SQL of course. When we're in the shell or in a libpq API call, it would be pretty hard to simulate a double quote, other than by means such as psql -d '"Foo"' which is surely worse than anything we've got now. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Peter Eisentraut wrote:
> ADBAAMD writes:
>
>> Brian T. Allen wrote:
>>
>>> I just ran into that too, and find it most undesirable. I don't know
>>> whether that is part of the SQL spec or not, but it seems very odd. The
>>> queries seem to be converted to lowercase before they ever reach the SQL
>>> engine.
>>
>> Correct me if I'm wrong, but doesn't it look like a trend in PostgreSQL
>> development? Instead of adding options and keeping the standard or
>> previous behaviour, a new behaviour is attached to old syntax, and if
>> you want to keep the standard or old results you have to do something else.
>>
>> The "Right Thing"(TM) to do in this case would be to be case insensitive
>> by default, according to the traditional (if not standard) SQL practice,
>> and do case sensitiveness if using quotes.
>
>
> The standard behaviour, which has been in PostgreSQL for the longest time,
I think that's the issue. It is the old behaviour in pgsql, but not the
SQL standard one. For people coming from ANSI SQL (or some
approximation of it) as myself, this can be confusing.
--
_
/ \ Leandro Guimarães Faria Corsetti Dutra +55 (11) 3040 8913
\ / Amdocs at Bell Canada +1 (514) 786 87 47
X Support Center, São Paulo, Brazil mailto:adbaamd@bell.ca
/ \ http://terravista.pt./Enseada/1989/ mailto:leandrod@amdocs.com
>> The "Right Thing"(TM) to do in this case would be to be case insensitive
>> by default, according to the traditional (if not standard) SQL practice,
>> and do case sensitiveness if using quotes.
The SQL spec says that double-quoted identifiers shall be treated
literally (ie, case sensitively), while unquoted identifiers shall be
folded to all upper case --- and *then* treated case sensitively. Thus,
foo FOO and "FOO" are the same name according to the spec, but "foo" and
"Foo" are two other names.
What Postgres does (and has done for a long time) is to treat
double-quoted identifiers literally while folding unquoted identifiers
to all lower case. So according to us, foo FOO and "foo" are the same
name while "FOO" and "Foo" are two other names.
Either way you get case-sensitive behavior for quoted identifiers
and non-case-sensitive behavior for unquoted ones. A query writer can
only tell that Postgres' behavior is not 100% SQL-compliant if he
writes the same name sometimes with quotes and sometimes without,
or if he looks in the system catalogs and expects to find his unquoted
table names there in a particular case.
I would not be particularly eager to see our behavior changed to get
that last little bit of spec-compliance, firstly because it would
doubtless break a lot of system-catalog-inspecting apps, and secondly
because I think the spec's choice of folding to upper case is a
brain-dead hangover from the days of teletypes. It's well-established
that lower case text is more easily readable than upper (because there's
more variation in the letter shapes). So I prefer to continue to see
table names in lower case when I look in the catalogs.
The question at hand was actually something different, because most of
what you use a database name for is outside the SQL parser proper.
It is not entirely practical to apply the SQL rules outside of SQL;
for example, if you expect
psql "FOO"
to act differently from
psql FOO
then you need to learn more about Unix shells. So our usual practice
outside of the SQL parser is just to take names literally (case
sensitively) without any foofahrah about downcasing if not quoted or
stripping quotes if present. An application could perhaps choose to
do downcasing/stripping on such names before sending them to the
postmaster, if it wanted to make database names act more like SQL names
inside its own context --- but I'd not advise doing that for names
coming from a Unix command line, because that creates a quoting mess.
(You wouldn't want to have to write psql '"FOO"' would you?)
I think there are some places that are not entirely consistent about
this; for example, IIRC pg_dump handles table names specified with -t
differently than it handles database names, which is probably a Bad
Idea. Volunteers to clean up such details are welcome...
regards, tom lane
ADBAAMD writes: > I think that's the issue. It is the old behaviour in pgsql, but not the > SQL standard one. For people coming from ANSI SQL (or some > approximation of it) as myself, this can be confusing. The behaviour in PostgreSQL *is* standard. There is no old behaviour. Which behaviour do you envision? -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Peter Eisentraut wrote:
> ADBAAMD writes:
>
>> I think that's the issue. It is the old behaviour in pgsql, but not the
>> SQL standard one. For people coming from ANSI SQL (or some
>> approximation of it) as myself, this can be confusing.
>
> The behaviour in PostgreSQL *is* standard. There is no old behaviour.
> Which behaviour do you envision?
Then I misunderstood everything. As far as I understood, pgsql behaviour
differs from ANSI SQL in the identifier name's case issue. I really
understand that it is more convenient as it is, the issue is when you
migrate from Oracle or other ANSIsh SQL db.
--
Leandro