Thread: 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
I found out about the quoting thing about 30 seconds after I made the post. :) Thanks everyone who replied.
Douglas McNaught wrote:
Douglas McNaught wrote:
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: 3D000You 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