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.