logo标签列表

Postgresql: 如何检查模式是否存在？

sqlpostgresql
85

我需要动态创建、管理和删除模式(schema)。如果我要创建的模式已经存在，我希望能够（通过外部手段）有条件地将其删除并按照指定重新创建。如何检查PostgreSQL 9服务器上是否存在该模式?

目前，我的做法是：

select exists (select * from pg_catalog.pg_namespace where nspname = 'schemaname');

但我感觉可能还有其他方法...这是查询Postgres特定schema存在的“正确”方式吗？

20
未来的访客注意：即将推出的Postgres 9.3将拥有“if not exists”创建模式。 - user330315
11个回答
128

以下查询将告诉您模式是否存在。

SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'name';
20
谢谢，这是一个很棒的回答。以下是我如何扩展它以完美地满足我的需求：SELECT exists(select schema_name FROM information_schema.schemata WHERE schema_name = 'foo'); （翻译：查询是否存在名为'foo'的模式） - Chris Cashwell
如何在C#中检查这个？如果我使用“ExecuteReader”，它会显示所有查询的1。 - Shesha
使用 ExecuteScalar 方法来获取 true 或 false 返回值。 - Adam Bruss
1
虽然这是一篇较旧的帖子，但我想发表我的看法（因为我正在使用同样的查询，但遇到了问题）。上面提到的特定表（schemata）只包含用户被授权拥有相关模式的模式。这是文档的链接。话虽如此，更好的解决方案可能是：`select distinct table_schema from information_schema.tables where table_schema = 'name'` - RoflWaffle17
使用单引号很重要！双引号对我不起作用。对于其他人来说可能很明显，但对我来说不是 :) - Francisco Gomes
70

如果您是完美主义者或想要节省一些时间，我建议您使用Postgres的本地系统目录。这样可以避免由调用pg_catalog引起的嵌套循环...

SELECT EXISTS(SELECT 1 FROM information_schema.schemata 
              WHERE schema_name = 'name');

查询information_schema

如果您直接查询pg_namespace：

SELECT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'name');

计划员的工作要简单得多：

enter image description here

因此你自己的解决方案是最好的。

1
它们实际上执行时间完全相同（0.034秒）。在这里说“几毫秒”真的有点牵强... - JCKödel
34

对于其他寻找条件模式创建的人可能有所帮助的相关内容。在我的一些创建脚本中，我发现自己使用了类似以下代码的方式：

DO $$
BEGIN

    IF NOT EXISTS(
        SELECT schema_name
          FROM information_schema.schemata
          WHERE schema_name = 'pgcrypto'
      )
    THEN
      EXECUTE 'CREATE SCHEMA pgcrypto';
    END IF;

END
$$;
1
这是有史以来最好的解决方案！ :-) - nagylzs
11
这可以是其中一种做法。首先删除模式，然后再创建它。
IF EXISTS:
Do not throw an error if the schema does not exist. A notice is issued in this case.

所以，
DROP SCHEMA IF EXISTS schema_Name
Create SCHEMA schema_Name
是的，但是假设我想要在其中加入一个步骤呢？例如：SchemaXYZ 存在吗？是：它包含 X 吗？否：删除并创建；是：跳出函数。 - Chris Cashwell
在那段时间里，我们必须执行select exists(...)。我找到了几个有同样需求的链接。它们也说了同样的话。 :) http://postgresql.1045698.n5.nabble.com/query-for-schema-existence-and-create-schema-in-jdbc-td3351491.html -- PostgreSQL http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/0c64f2ce-0ed8-4aee-b85c-17bb660a154a --- SQL Server - Vinay
当有很多检查模式的方法时，通过检查是否出现错误来检查简单条件是一种可怕的方法，正如其他人所指出的那样。 - Guy Park
9
如果您想创建一个架构（schema），如果它不存在，只需执行以下操作:
CREATE SCHEMA IF NOT EXISTS foo

来源：https://www.postgresql.org/docs/current/sql-createschema.html

CREATE SCHEMA命令用于创建模式。 模式只是命名空间的集合，用于将数据库对象分组在一起。 CREATE SCHEMA命令除了指定模式名称之外，还可以指定该模式的所有者和架构授权。
示例：
创建名为sales的模式：
CREATE SCHEMA sales;
创建名为inventory的模式，并将其所有权授予用户bob：
CREATE SCHEMA inventory AUTHORIZATION bob;
3
这适用于PostgreSQL，它会检查模式是否存在，如果不存在，则会创建它：
CREATE SCHEMA IF NOT EXISTS tenant;
1

使用

SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_namespace WHERE nspowner <> 1 AND nspname = 'schemaname');

如果您查看https://www.postgresql.org/docs/current/static/infoschema-schemata.html，您会发现：
视图“schemata”包含当前数据库中当前用户可以访问的所有模式（通过拥有者或某些特权）。
这意味着使用information_schema.schemata的已接受答案中的查询不会显示当前用户不是所有者或没有USAGE特权的模式。
SELECT 1
FROM pg_catalog.pg_namespace
WHERE nspowner <> 1 -- ignore tables made by postgres itself
AND nspname = 'schemaname';

这是更完整的，将显示所有现有模式，无论您是否可以访问该模式，而PostgreSQL本身没有创建。

1
0

这个对我有效（Postgres 9.3）：

Select exists (SELECT 1 FROM information_schema.schemata where catalog_name = 'My_BD_with_UpperCase_characters_in_its_Name')
0

有没有其他方法可以检查模式存在？

to_regnamespace ( text ) → regnamespace

将文本模式名称翻译为其OID。通过将字符串转换为regnamespace类型（参见第8.19节），可以获得类似的结果；但是，如果未找到名称，此函数将返回NULL而不是抛出错误。
因此，您可以使用以下代码：
select to_regnamespace('public') is not null;
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接