Cannot create a new table after "DROP SCHEMA public"
The error message pops up when none of the schemas in your search_path
can be found.
Either it is misconfigured. What do you get for this?
SHOW search_path;
Or you deleted the public
schema from your standard system database template1
. You may have been connected to the wrong database when you ran drop schema public cascade;
As the name suggests, this is the template for creating new databases. Therefore, every new database starts out without the (default) schema public
now - while your default search_path
probably has 'public' in it.
Just run (as superuser public
or see mgojohn's answer):
CREATE SCHEMA public;
in the database template1
(or any other database where you need it).
The advice with DROP SCHEMA ... CASCADE
to destroy all objects in it quickly is otherwise valid.
That advice can cause some trouble if you have an application user (like 'postgres') and run the DROP/CREATE commands as a different user. This would happen if, for example, you're logged in as 'johndoe@localhost' and simply hit psql mydatabase. If you do that, the new schema's owner will be johndoe, not 'postgres' and when your application comes along to create the tables it needs, it wont see the new schema.
To give ownership back to your application's user (assuming that user is 'postgres'), you can simply run (form the same psql prompt as your local user):
ALTER SCHEMA public OWNER to postgres;
and you'll be all set.