Since I wanted to drop some tables and somebody suggested the below and I did it:
postgres=# drop schema public cascade;
DROP SCHEMA
postgres=# create schema public;
CREATE SCHEMA
Then I got problem when creating a new database, such as:
postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table hi(id int primary key);
*ERROR: no schema has been selected to create in*
You can see I got error
ERROR: no schema has been selected to create in*
How can I restore the public schema?
I suggest people never do "drop schema public cascade;" if we don't know how to restore. Can somebody help me out?
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.