3

When I do a: DROP DATABASE mydb; and subsequently: CREATE DATABASE mydb WITH OWNER user; the new mydb database is created with all the tables and data that I had in the database I just dropped. What am I be missing?

2 Answers 2

6

You probably created some tables (with data) in the database template1.

Connect to that database (e.g.: psql postgres template1) and drop everything you don't need there.

More details in the manual: http://www.postgresql.org/docs/current/static/manage-ag-templatedbs.html

0

You may be falling afoul of one of the restrictions on the command:

  1. It can only be executed by the database owner.
  2. It cannot be executed while you or anyone else are connected to the target database.
  3. It cannot be executed inside a transaction block.
2
  • The problem is not that he/she can not create the database - the database is created. The problem is that the (newly) created database contains stuff that shouldn't be in there.
    – user1822
    Jan 16, 2014 at 21:44
  • Depending on how they're doing the drop/create (psql/pgAdmin/script), an unnoticed failure to drop will mean that the data never got removed and the subsequent create will fail but the data will still be there. It looks like you correctly identified the problem though.
    – dommage
    Jan 16, 2014 at 21:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.