Re: [GENERAL] Error in ALTER DATABASE command
--- On Tue, 9/23/08, William Garrison [EMAIL PROTECTED] wrote: From: William Garrison [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error in ALTER DATABASE command
On Tue, Sep 23, 2008 at 11:49 AM, William Garrison [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error in ALTER DATABASE command
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error in ALTER DATABASE command
I found out about the quoting thing about 30 seconds after I made the post. :) Thanks everyone who replied. Douglas McNaught wrote: On Tue, Sep 23, 2008 at 11:49 AM, William Garrison [EMAIL PROTECTED] 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
