SQL Postgresql：检查架构是否存在？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7016419/
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
Postgresql: Check if Schema Exists?
提问by Chris Cashwell
I need to create, manage and drop schemas on the fly. If I go to create a schema that already exists, I want to (conditionally, via external means) drop and recreate it as specified. How can I check for the existence of said schema on my Postgres 9 server?
我需要动态创建、管理和删除模式。如果我要创建一个已经存在的模式，我想（有条件地，通过外部方式）删除并按照指定重新创建它。如何检查 Postgres 9 服务器上是否存在上述模式？
Currently, I'm doing this:
目前，我正在这样做：
select exists (select * from pg_catalog.pg_namespace where nspname = 'schemaname');
but I feel like there's probably another way... is this the "proper" way to query Postgres for the existence of a particular schema?
但我觉得可能还有另一种方式......这是查询 Postgres 是否存在特定模式的“正确”方式吗？
回答by Peter Eisentraut
The following query will tell you whether a schema exists.
以下查询将告诉您模式是否存在。
SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'name';
回答by Borys
If you are a total purist or you want to gain some milisecs. I recommend you to make use of postgres native system catalog. One can avoid then nested loop which is caused by calling pg_catalog anyway...
如果您是一个纯粹主义者，或者您想获得一些毫秒。我建议您使用 postgres 本机系统目录。无论如何，可以避免由调用 pg_catalog 引起的嵌套循环......
SELECT EXISTS(SELECT 1 FROM information_schema.schemata
WHERE schema_name = 'name');
If you querying pg_namespace directly:
如果直接查询 pg_namespace：
SELECT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'name');
Planer's work is much simpler:
Planer的工作要简单得多：
So your own solution was the best.
所以你自己的解决方案是最好的。
回答by Glenn
Somewhat related and perhaps of interest to others looking for conditional schema creation. I found myself using code like this in some of my creation scripts:
与寻找条件模式创建的其他人有些相关并且可能感兴趣。我发现自己在一些创建脚本中使用了这样的代码：
DO $$
BEGIN
IF NOT EXISTS(
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name = 'pgcrypto'
)
THEN
EXECUTE 'CREATE SCHEMA pgcrypto';
END IF;
END
$$;
回答by Vinay
This can be one of the approaches. Drop the schema first and then create it.
这可以是其中一种方法。首先删除架构，然后创建它。
IF EXISTS:
Do not throw an error if the schema does not exist. A notice is issued in this case.
So,
所以，
DROP SCHEMA IF EXISTS schema_Name
Create SCHEMA schema_Name
回答by Tay Ray Chuan
From http://www.postgresql.org/docs/9.1/static/infoschema-schemata.html(emphasis my own):
来自http://www.postgresql.org/docs/9.1/static/infoschema-schemata.html（强调我自己的）：
The view schemata contains all schemas in the current database that are owned by a currently enabled role.
视图模式包含当前数据库中由当前启用的角色拥有的所有模式。
So your original solution/query is more reliable than Peter's, albeit non-standard.
所以你的原始解决方案/查询比彼得的更可靠，尽管是非标准的。
回答by gyosifov
If you want to create a schema if it doesn't exist you can just execute:
如果你想创建一个不存在的模式，你可以执行：
CREATE SCHEMA IF NOT EXISTS foo
Source: https://www.postgresql.org/docs/current/sql-createschema.html
来源：https: //www.postgresql.org/docs/current/sql-createschema.html
回答by Broken_Window
This one worked for me (Postgres 9.3):
这个对我有用（Postgres 9.3）：
Select exists (SELECT 1 FROM information_schema.schemata where catalog_name = 'My_BD_with_UpperCase_characters_in_its_Name')
回答by m0meni
Use
用
SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_namespace WHERE nspowner <> 1 AND nspname = 'schemaname');
If you check https://www.postgresql.org/docs/current/static/infoschema-schemata.html, you see
如果您检查https://www.postgresql.org/docs/current/static/infoschema-schemata.html，您会看到
The view schemata contains all schemas in the current database that the current user has access to(by way of being the owner or having some privilege).
视图模式包含当前用户有权访问的当前数据库中的所有模式（通过成为所有者或具有某些特权）。
This means the query in accepted answer using information_schema.schematadoesn't show schemas that the current user isn't the owner of or doesn't have the USAGEprivilege on.
这意味着接受的答案中的查询 usinginformation_schema.schemata不显示当前用户不是其所有者或没有USAGE权限的模式。
SELECT 1
FROM pg_catalog.pg_namespace
WHERE nspowner <> 1 -- ignore tables made by postgres itself
AND nspname = 'schemaname';
is more complete and will show all existing schemas that postgres didn't make itself regardless of whether or not you have access to the schema.
更完整，并且将显示 postgres 没有自行创建的所有现有模式，无论您是否有权访问该模式。
回答by Junior Chester
NONE of those will work if you have objects (tables,sprocs,views) within a particular schema - IT WILL FAIL during DROP...
如果您在特定模式中有对象（表、过程、视图），这些都不会起作用 - 在 DROP 期间它会失败......
CREATE & MANAGE is the easy part.. It's the drop that will get you.. Anyways, I couldn't find a suitable answer, so I posted here for others..
CREATE & MANAGE 是最简单的部分.. 它会让你受益匪浅.. 无论如何，我找不到合适的答案，所以我在这里发布给其他人..
请参见此处的链接：http : //social.msdn.microsoft.com/Forums/en-US/transactsql/thread/4753d1b8-f547-44c6-b205-aa2dc22606ba/#6eb8238a-305e-40d5-858e-0f48104
