database “DROP SCHEMA public”后无法创建新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14285854/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot create a new table after "DROP SCHEMA public"
提问by user1342336
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?
如何恢复公共架构？
我建议人们永远不要做“删除模式公共级联”；如果我们不知道如何恢复。有人可以帮我吗？
采纳答案by Erwin Brandstetter
The error message pops up when none of the schemas in your search_pathcan be found.
Either it is misconfigured. What do you get for this?
当search_path找不到您中的任何架构时，会弹出错误消息。
要么是配置错误。你能得到什么？
SHOW search_path;
Or you deleted the publicschema from your standard system database template1. You may have been connected to the wrong databasewhen you ran drop schema public cascade;
或者您public从标准系统数据库中template1删除了架构。您可能已经连接到错误的数据库，当你跑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 publicnow - while your default search_pathprobably has 'public' in it.
顾名思义，这是创建新数据库的模板。因此，每个新数据库public现在都没有（默认）架构- 而您的默认架构search_path可能包含“public”。
Just run (as superuser publicor see mgojohn's answer):
只需运行（作为超级用户public或查看mgojohn 的回答）：
CREATE SCHEMA public;
in the database template1(or any other database where you need it).
在数据库template1（或您需要的任何其他数据库）中。
The advice with DROP SCHEMA ... CASCADEto destroy all objects in it quickly is otherwise valid.
DROP SCHEMA ... CASCADE快速销毁其中的所有对象的建议在其他方面是有效的。
回答by mgojohn
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.
如果您有一个应用程序用户（如“postgres”）并以不同的用户身份运行 DROP/CREATE 命令，那么该建议可能会导致一些麻烦。例如，如果您以“johndoe@localhost”的身份登录并简单地点击 psql mydatabase，就会发生这种情况。如果你这样做，新模式的所有者将是 johndoe，而不是“postgres”，当你的应用程序来创建它需要的表时，它不会看到新模式。
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):
要将所有权归还给应用程序的用户（假设用户是“postgres”），您可以简单地运行（形成与本地用户相同的 psql 提示符）：
ALTER SCHEMA public OWNER to postgres;
and you'll be all set.
一切准备就绪。
