Can't delete database

Question

I'm new to PostgreSQL and am having a problem with what I perceive to be a simple command DROP DATABASE and DROPDB. Why would the following commands not delete my database?

postgres=# drop database clientms
postgres-# \l
                                    List of databases
   Name    |    Owner     | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+--------------+----------+-------------+-------------+-----------------------
 clientms  | clientmsuser | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 postgres  | postgres     | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 template0 | postgres     | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |              |          |             |             | postgres=CTc/postgres
 template1 | postgres     | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |              |          |             |             | postgres=CTc/postgres
(4 rows)

postgres-# dropdb clientms
postgres-# \l
                                    List of databases
   Name    |    Owner     | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+--------------+----------+-------------+-------------+-----------------------
 clientms  | clientmsuser | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 postgres  | postgres     | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 template0 | postgres     | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |              |          |             |             | postgres=CTc/postgres
 template1 | postgres     | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |              |          |             |             | postgres=CTc/postgres
(4 rows)

https://stackoverflow.com/questions/12472026 – Mar 26 '20 at 08:25 — , Mar 26 '20 at 08:25

Erwin Brandstetter · Accepted Answer · 2023-03-15T07:16:03.363

34

You forgot the semicolon ; after the command. Try:

DROP DATABASE clientms;

The incomplete command is also indicated by the prompt: - instead of =. This is to allow multi-line commands.

For other cases, not hinging on the simple syntax issue:

Dropping DB when there are active connections

edited Mar 15 '23 at 07:16

answered Jun 06 '12 at 10:51

Erwin Brandstetter

605,456
145
1,078
1,228

Yup! People leave it out a lot on the postings on the subject that I've seen :( – Robert Johnstone Jun 06 '12 at 11:05
Depending on the client, the semicolon may not be required for a single command. It's required in interactive psql though. – Erwin Brandstetter Jun 06 '12 at 11:34
1

You can use -S or --single-line to get rid of the required semicolon. – Frank Heikens Jun 06 '12 at 11:44

score 2 · Answer 2 · edited Mar 26 '20 at 09:51

2

With PGAdmin: Check if you're connected with server, go to your database and right click it and drop the database. Refresh the database or Re-open the server again!

Doing it manually:

$ sudo -u postgres psql
# \l                         // check db in list
# q                          // to quit that menu
# Drop Database <db_name>;

edited Mar 26 '20 at 09:51

Robert Johnstone

5,431
12
58
88

answered Mar 26 '20 at 08:22

Gaurav

479
4
5

score 1 · Answer 3 · answered Oct 01 '17 at 20:15

1

Annoyingly I'd already solved this problem, then encountered it again a year and a bit later and solved it a different way because I didn't notice that I'd already got notes on how to fix it. Here are my notes:

http://www.itsupportforum.net/topic/unable-to-delete-drop-postgresql-database/

In summary, I couldn't delete the db because it was in use by the interface I was using to delete it. Dumb.

answered Oct 01 '17 at 20:15

Boris

11
1

A link to a solution is welcome, but please ensure your answer is useful without it - **quote the most relevant part of the solution** from the page you're linking to, in case the target page is unavailable. Answers that are little more than a link may be deleted. – SherylHohman Oct 01 '17 at 20:55

score 0 · Answer 4 · edited Jun 06 '12 at 12:22

0

Apparently destroydb clientms works (took me a lot of digging though) [link]

edited Jun 06 '12 at 12:22

Erwin Brandstetter

605,456
145
1,078
1,228

answered Jun 06 '12 at 10:57

Robert Johnstone

5,431
12
58
88

No such command. I am guessing you mean `destroydb`, which is the ancient form of the modern day command line tool [`dropdb`](http://www.postgresql.org/docs/current/interactive/app-dropdb.html). – Erwin Brandstetter Jun 06 '12 at 11:44
1

The difference is that `destroydb` (or rather, the command-line `dropdb` tool) isn't run over the `psql` prompt, but via a unix shell or Windows' cmd.exe. Commands on unix shells and cmd.exe are terminated by a newline, not a semicolon, so you actually ran the command when working on a shell. – Craig Ringer Jun 07 '12 at 06:38

Can't delete database

4 Answers4

Linked

Related