DROP database doesn't appear to drop all database objects

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?

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.

StackExchange Database Administrators Q#215719, answer score: 3