Thread: Using drop database from psql

When I try to drop a database, whether I have opened it or some other
database, I get told it doesn't exist. The message changes the case of
the database name, but that may be a feature of SQL.

They system is a Fedore Core 3 server running
postgresql-server-7.4.6-1.FC3.1.

Here is the output:

[postgres@oz ~]$ psql -d template1
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# \l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+-----------
 SCM       | postgres | SQL_ASCII
 SCMTST    | lbarnes  | SQL_ASCII
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(4 rows)

template1=# \q
[postgres@oz ~]$ psql -d SCM
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

SCM=# drop database SCM;
ERROR:  database "scm" does not exist



Can anyone cast light on this? The database is empty, and I want to
recreate it with a different owner (which may be a task more easily done
with some other method).

--
Lan Barnes                    lan@falleagle.net
Linux Guy, SCM Specialist     858-354-0616

Lan Barnes <lan@falleagle.net> writes:
> SCM=# drop database SCM;
> ERROR:  database "scm" does not exist

> Can anyone cast light on this?

Case-folding.  Within SQL you'd need to refer to that database as
"SCM" (with the double quotes).  We don't do case-folding for names
used on the shell command line, but within SQL this is per spec.  See
the last paragraphs in this section:
http://www.postgresql.org/docs/7.4/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

            regards, tom lane

Lan,

Beside Tom's comments, you won't be allowed to drop the database while you are working on it.
(no drop database will succceed if users are still working).  So, connect to template1 and fire the SQL with the quotes (dixit Tom Lane) or just use the dropdb command line tool


SCM=# drop database SCM;
ERROR:  database "scm" does not exist

Regards,
Guido Barosio




--
/"\   ASCII Ribbon Campaign  .
\ / - NO HTML/RTF in e-mail  .
X  - NO Word docs in e-mail .
/ \ -----------------------------------------------------------------