Re: Re: Database Name Case Sensitivity - Mailing list pgsql-general

>> 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