psql how to drop current database

Question

Trying to drop the primary database "foo", I get the error

ERROR: cannot drop the currently open database

Whats the right way to drop the primary psql database?

https://dba.stackexchange.com/questions/11893/force-drop-db-while-others-may-be-connected — Mitch Wheat, Mar 07 '18 at 01:26
@MitchWheat that question is marked as off topic (so I coudln't post a relevant-to-seo answer), and doesn't include anything about the target database being current database. - also fair ot point out dba stackex may have good resources — ThorSummoner, Mar 07 '18 at 01:30
The fact that the linked question was marked as off-topic makes it likely this is also. — Mitch Wheat, Mar 07 '18 at 01:39

score 40 · Accepted Answer · answered Mar 07 '18 at 08:36

40

That's what the database postgres is there for.

Connect to it to drop your database.

answered Mar 07 '18 at 08:36

Laurenz Albe

This is the best answer by far. Thanks :) – Chef1075 Apr 26 '18 at 00:37
Plus: 1. Disconnect the database you want to drop 2. Drop it via drop database myDatabase – olivmir Sep 01 '20 at 10:03
This is the only correct answer to do it with psql – J88 Aug 20 '21 at 08:30
You cannot drop a database by using a session of the same database in some versions of PostgreSQL. Connect to another database and then run command DROP DATABASE DB; – JustBeingHelpful May 02 '22 at 16:31
1

@MacGyver Right, that's what my answer recommends. Unless you need to drop the `postgres` database, then you have to connect to a different database. But `postgres` is typically left empty, and you shouldn't feel a need to drop it. – Laurenz Albe May 03 '22 at 02:30
Sorry for downvoting you. I upvoted you on another to make up for it. – JustBeingHelpful May 03 '22 at 14:33
@MacGyver No problem, these are just brownie points anyway. You should be able to un-downvote however. – Laurenz Albe May 03 '22 at 14:35
Amazing, I didn't know about this `postgres` empty database. I've struggled to find documentation about it though, why / when / how does this database get created? Is it generated when Postgresql is installed on the server? – benj-p Apr 03 '23 at 14:09
1

@benj-p It is created when you create the database cluster with `initdb`. – Laurenz Albe Apr 03 '23 at 14:14

score 7 · Answer 2 · edited Mar 07 '18 at 01:38

7

Create a new database to log into while you drop the original one - I guess?

create database _;
(relog with -d _ )
drop database dbname;
create database dbname;
(relog)
drop database _;

edited Mar 07 '18 at 01:38

Mitch Wheat

answered Mar 07 '18 at 01:24

ThorSummoner

creating a new database is definitely unnecessary extra steps. connect directly to an internal database like `postgres` – Sampson Crowley Jan 12 '22 at 20:11
off the top of my head, i vaguely recall the target database to drop being `postures`; even in that case, template0 might exist? well in any case, if a temporary db is created it will exist regardless of the target db to operate on. cheers – ThorSummoner Jan 13 '22 at 02:52
idk what your `postures` database is, but `postgres` database _always_ exists. there is no need to create a new temporary database just to drop a database. switch to `postgres` and drop the database. your method _forces_ you to create a new one, or keep a temporary one – Sampson Crowley Jan 13 '22 at 04:23
oh excuse the typo, "the target database to drop being `postgres`" (the one that will always exist), in my experiments, this database did appear to tolerate being dropped and did not exist after it was dropped. this comment recited from older memory – ThorSummoner Jan 15 '22 at 19:47

2 Answers2