databasepostgresqldatabase-schemacreate-tablesql-drop

Cannot create a new table after "DROP SCHEMA public"

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?

Solution

  • The error message pops up when none of the schemas in your search_path can be found. Either search_path 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 default template for creating new databases. So 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 postgres (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.