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?

Erwin Brandstetter · Accepted Answer · 2024-01-18 23:02:30Z

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.

Super! Thank you so much! Yes, after I run 'CREATE SCHEMA public;' in template1, I can create new database in public schema.
@user1342336: Cool. :) Just to be clear: a schema is in a database, not the other way round. DB cluster -> DB -> schema -> table.

mgojohn · Accepted Answer · 2014-10-08 15:46:00Z

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.

Collectives™ on Stack Overflow

Cannot create a new table after "DROP SCHEMA public"

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related