Обсуждение: Error in ALTER DATABASE command

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?

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

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