Error in ALTER DATABASE command

On Tue, Sep 23, 2008 at 11:49 AM, William Garrison
<postgres@mobydisk.com> wrote:

In Postgresql 8.2.9 on Windows, you cannot rename a database if the name
contains mixed case.

3) Open a query window, or use PSQL to issue the following command
ALTER DATABASE MixedCase RENAME TO anything_else;
PostgreSQL will respond with:
ERROR: database "mixedcase" does not exist
SQL state: 3D000

You need to quote the identifier using double-quotes to avoid
case-folding (this is a general rule):

ALTER DATABASE "MixedCase" RENAME TO anything_else;

Postgresql seems to force many things to lower case.

Yes, it's actually a variation on the SQL standard, which specifies
forcing to upper case (as Oracle does).

Is it a bug that the
admin tool lets you create a database with mixed case names?

The admin tool is a separate project and works by its own rules.

Or is it a bug
that you cannot rename them thereafter?

The FAQ has a good section on identifier case-folding and quoting, I
think. Probably worth a read.

-Doug

On 23/09/2008 16:49, William Garrison wrote:

In Postgresql 8.2.9 on Windows, you cannot rename a database
if the name contains mixed case.

Yes you can, in 8.3 anyway:

postgres=# create database "TeSt";
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding
------------------+----------+----------
[snip]
teSt | postgres | UTF8
[snip]

postgres=# alter database "TeSt" rename to "tEsT";
ALTER DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding
------------------+----------+----------
[snip]
tEsT | postgres | UTF8
[snip]

3) Open a query window, or use PSQL to issue the following command
ALTER DATABASE MixedCase RENAME TO anything_else; PostgreSQL will
respond with: ERROR: database "mixedcase" does not exist SQL state:
3D000

Have you tried quoting the database name, thus? -

ALTER DATABASE "MixedCase" RENAME TO anything_else;

This ought to do it.

This does not happen if you create the database using a manual query
in pgadmin, or if you use psql. Both of those tools will create the
database as "mixedcase" instead of "MixedCase"

[snip]

Postgresql seems to force many things to lower case. Is it a bug
that the admin tool lets you create a database with mixed case names?
Or is it a bug that you cannot rename them thereafter?

No, it's PostgreSQL's (well-documented) behaviour - as you noted, it
folds names to lower-case unless you specifically quote them. I'd hazard
a guess that PgAdmin is quoting the database name behind the scenes,
hence you can create mixed-case names.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

William Garrison wrote:

In Postgresql 8.2.9 on Windows, you cannot rename a database if the
name contains mixed case.

To replicate:
1) Open the pgadmin tool.
2) Create a database named "MixedCase" (using the UI, not using a
query window or using PSQL)
3) Open a query window, or use PSQL to issue the following command
ALTER DATABASE MixedCase RENAME TO anything_else;
PostgreSQL will respond with:
ERROR: database "mixedcase" does not exist
SQL state: 3D000

Did you try:
ALTER DATABASE "MixedCase"....?

Note the use of double-quotes around the name. I don't have any Windows
machines to try this with but it appears you just need to add the
double-quotes to prevent PostgreSQL from folding to lower-case.

Cheers,
Steve

I found out about the quoting thing about 30 seconds after I made the
post. :) Thanks everyone who replied.

Douglas McNaught wrote:

--- On Tue, 9/23/08, William Garrison <postgres@mobydisk.com> wrote:

From: William Garrison <postgres@mobydisk.com>
Subject: [GENERAL] Error in ALTER DATABASE command
To: "Postgres General List" <pgsql-general@postgresql.org>
Date: Tuesday, September 23, 2008, 3:49 PM
In Postgresql 8.2.9 on Windows, you cannot rename a database
if the name
contains mixed case.

To replicate:
1) Open the pgadmin tool.
2) Create a database named "MixedCase" (using the
UI, not using a query
window or using PSQL)
3) Open a query window, or use PSQL to issue the following
command
ALTER DATABASE MixedCase RENAME TO anything_else;
PostgreSQL will respond with:
ERROR: database "mixedcase" does not exist
SQL state: 3D000

This does not happen if you create the database using a
manual query in
pgadmin, or if you use psql. Both of those tools will
create the
database as "mixedcase" instead of
"MixedCase"

I am using:
"PostgreSQL 8.2.9 on i686-pc-mingw32, compiled by GCC
gcc.exe (GCC)
3.4.2 (mingw-special)"

I guess for now, I have to dump and reload my database. :(

Postgresql seems to force many things to lower case. Is it
a bug that
the admin tool lets you create a database with mixed case
names? Or is
it a bug that you cannot rename them thereafter?

error i dont think so, teh pgadmin create the object whit the double quote (") implicit. Rename the database
ALTER DATABASE "MixedCase" RENAME TO mixedcase