How to conditionally drop a database in postgres?

Question

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?

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
– Adrian Klaver, Commented May 16, 2021 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
– Chris F Carroll, Commented May 17, 2021 at 8:21

Laurenz Albe · Accepted Answer · 2021-05-16 16:57:58Z

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.

answered May 16, 2021 at 16:57

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to conditionally drop a database in postgres?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related