0

Whereas How to drop a table based on IF condition in postgres? solves the problem of conditionally dropping a table, it doesn't work for dropping a database because:

ERROR:  DROP DATABASE cannot be executed from a function

I want to drop a database but only if it is has no user tables. How can I execute any kind of IF if I can't do it from function?

Chris F Carroll
  • 11,146
  • 3
  • 53
  • 61
  • 1
    The function issue is a subset of `DROP DATABASE` not being allowed in a transaction. Functions run in a transaction, hence the error. This is going to be something you need to do from outside Postgres using a script. – Adrian Klaver May 16 '21 at 17:15
  • Ah. So the best I can do is to move the `IF` control out of the postgres script into the hosting shell script; and then I can ask psql to drop database unconditionally – Chris F Carroll May 17 '21 at 08:21

1 Answers1

0

You'll first have to connect to the database, count the tables, connect to a different database (for example postgres), revoke connections to the database from everybody, kill all database sessions and run DROP TABLE.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263