DROP database doesn't appear to drop all database objects

Question

I am kind of new to Postgres and am using recent version on Mac. I'm writing a .sql script that will create a database and tables and load data. (I need to be able to automate this, so I will be running it in a cron job, I will NOT be running it from within psql shell.) Every time I run, I want to start from a clean slate, wiping out the database and all its objects.

When I run the below commands from bash in a .sql file with this content:

DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
CREATE TABLE progress (
foo character varying(10)
);

Then

psql -U postgres -d some_db -a -f db_etl.sql

results in:

DROP DATABASE IF EXISTS mydb;
DROP DATABASE
CREATE DATABASE mydb;
CREATE DATABASE
CREATE TABLE progress (
 foo character varying(10)
);
psql:db_etl.sql:17: ERROR: relation "progress" already exists

How could the relation still exist if the DROP of its database was successful? Do I need to be using fully qualified object names like my_db.public.progress?

Answer 1

How could the relation still exist if the DROP of its database was successful? Do I need to be using fully qualified object names like my_db.public.progress?

You can't DROP the database you're connected to.

psql -U postgres -d some_db -a -f db_etl.sql

That shows you're connecting to some_db, then you're trying to recreate the database mydb and then you're trying to create the table progress on some_db.

If you want to create the table progress on the newly created mydb you'll have to use psql -d mydb or \connect to it explicitly in psql after you create it.

• David: solution is to add \c mydb after the creation of that db.

Answer 2

You can't drop the database you are currently connected to. You are connected to some_db, not mydb. You drop mydb, then create mydb agsin, but you don't change your connection from some_db to mydb. Creating a database does not automatically change your connection to the newly created database. So the table you created is attempted to be created in some_db, where it evidently already exists.

You can't create objects in a database you are not connected to, so fully qualifying the object name will not help. You can use the psql meta-command \c to change your connected database in the .sql script. Of course you have to go through authentication again.