Cannot create a new table after "DROP SCHEMA public"

Question

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 1

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.

Solution 2

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.

Comments

[user1342336]

Super! Thank you so much! Yes, after I run 'CREATE SCHEMA public;' in template1, I can create new database in public schema.

[Erwin Brandstetter]

@user1342336: Cool. :) Just to be clear: a schema is in a database, not the other way round. DB cluster -> DB -> schema -> table.

[user1342336]

Great! Thanks a lot for clarifying that for me!