Re: Error in ALTER DATABASE command

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(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------