{"id":2290,"date":"2025-03-08T09:49:28","date_gmt":"2025-03-08T02:49:28","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=2290"},"modified":"2025-03-12T20:01:38","modified_gmt":"2025-03-12T13:01:38","slug":"postgresql-drop-database","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-database-administration\/postgresql-drop-database\/","title":{"rendered":"PostgreSQL DROP DATABASE Statement"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to drop a database using the PostgreSQL DROP DATABASE<\/code> statement.<\/p>\n\n\n\n

PostgreSQL DROP DATABASE statement overview #<\/a><\/h2>\n\n\n\n

The DROP DATABASE<\/code> statement permanently deletes a database, including all its objects such as tables, views<\/a>, indexes<\/a>, and data. This action is irreversible<\/strong>.<\/p>\n\n\n\n

Here’s the basic syntax of the DROP DATABASE<\/code> statement:<\/p>\n\n\n

DROP<\/span> DATABASE<\/span> database_name;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the statement removes the database named database_name<\/code>.<\/p>\n\n\n\n
PostgreSQL does not allow you to drop the current database. To drop a database, you must connect to a different database<\/strong>. Additionally, only the database owner<\/a> or superuser<\/a> can drop a database.<\/p>\n\n\n\n
Additionally, if a database has any active connections, the DROP DATABASE<\/code> statement will fail.<\/p>\n\n\n\n
If you want to drop a database with active connections, you can terminate these connections before dropping the database using the FORCE<\/code> option:<\/p>\n\n\n
DROP<\/span> DATABASE<\/span> database_name\nWITH<\/span> (FORCE);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Dropping a database example #<\/a><\/h2>\n\n\n\n
First, open the terminal and connect to your PostgreSQL database server using psql tool:<\/p>\n\n\n
psql -U postgres<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> DATABASE<\/span> crm;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, switch to the crm<\/code> database:<\/p>\n\n\n
\\c crm<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, drop the crm<\/code> database:<\/p>\n\n\n
DROP<\/span> DATABASE<\/span> crm;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL issued the following error:<\/p>\n\n\n
ERROR: cannot drop<\/span> the currently open<\/span> database<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
To drop the crm<\/code> database, you need to switch to a different database.<\/p>\n\n\n\n
Fifth, switch to the postgres<\/code> database:<\/p>\n\n\n
\\c postgres<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, drop the crm<\/code> database:<\/p>\n\n\n
DROP<\/span> DATABASE<\/span> crm;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Dropping a database with the FORCE option #<\/a><\/h2>\n\n\n\n
We’ll create a new database, connect to it, and drop the database from a separate session.<\/p>\n\n\n\n
Session 1 #<\/a><\/h3>\n\n\n\n
First, connect to your PostgreSQL server using psql<\/code> tool:<\/p>\n\n\n
psql -U postgres<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a new database named finance<\/code>:<\/p>\n\n\n
CREATE<\/span> DATABASE<\/span> finance;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, switch to the finance<\/code> database:<\/p>\n\n\n
\\c finance<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Session 2 #<\/a><\/h3>\n\n\n\n
First, connect to your PostgreSQL in a different session:<\/p>\n\n\n
psql -U postgres<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, attempt to drop the finance<\/code> database:<\/p>\n\n\n
DROP<\/span> DATABASE<\/span> finance;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL issued the following error:<\/p>\n\n\n
ERROR: database<\/span> \"finance\" is<\/span> being accessed by<\/span> other users\nDETAIL: There is<\/span> 1<\/span> other session<\/span> using<\/span> the database<\/span>.<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, check the active connections using the following query:<\/p>\n\n\n
SELECT<\/span>\n  pid,\n  usename,\n  application_name,\n  client_addr\nFROM<\/span>\n  pg_stat_activity\nWHERE<\/span>\n  datname = 'finance'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
  pid  | usename  | application_name | client_addr\n-------+----------+------------------+-------------\n 30400 | postgres | psql             | 127.0.0.1<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, drop the finance<\/code> database with the FORCE<\/code> option:<\/p>\n\n\n
DROP<\/span> DATABASE<\/span> finance WITH<\/span> (FORCE);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
DROP<\/span> DATABASE<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the statement successfully closes the active connection and drops the database.<\/p>\n\n\n\n
Session 1 #<\/a><\/h3>\n\n\n\n
If you attempt to run any query on session 1 connected to the finance<\/code> database, you’ll encounter an error. For example:<\/p>\n\n\n
SELECT<\/span> 1<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Error:<\/p>\n\n\n
server<\/span> closed the connection<\/span> unexpectedly\n        This probably means the server<\/span> terminated abnormally\n        before<\/span> or<\/span> while<\/span> processing the request.\nThe connection<\/span> to<\/span> the server<\/span> was lost. Attempting reset<\/span>: Failed.\nThe connection<\/span> to<\/span> the server<\/span> was lost. Attempting reset<\/span>: Failed.<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the DROP DATABASE<\/code> statement to delete a database from the PostgreSQL server.<\/li>\n\n\n\n
  • Use the DROP DATABASE<\/code> with the FORCE<\/code> option to close active connections and drop the database.<\/li>\n\n\n\n
  • Only the superuser<\/code> and database owner can drop the database.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, create a new database<\/a> named crm<\/code>:<\/p>\n\n\n
When you connect to a database, it becomes the current database<\/a>.<\/p>\n\n\n\n