如何检查 PostgreSQL 的 public 模式是否存在？

Question

运行以下查询：

SELECT exists (
    SELECT
        schema_name 
    FROM
        information_schema.schemata 
    WHERE
        schema_name = 'public'
) AS schema_exists;

即使公共模式存在，我始终得到FALSE。我该如何检查此模式是否存在？

编辑

我正在使用PostgreSQL 8.4版本。

- Marcio Mazzucato

---

当我在本地运行它时，它对我返回 true（作为布尔值）。你是如何检查返回值的？ - Ruslan

@Ruslan，我正在使用PostgreSQL 8.4版本。你用的是哪个版本？ - Marcio Mazzucato

@SaUce，是的，我尝试过了，但是结果为空。 - Marcio Mazzucato

在这种情况下，你的查询是正确的，它永远不会返回true。你确定你正在正确的数据库上执行这个查询，并且你的用户有足够的权限来读取它吗？ - user275683

@Ruslan，使用 SELECT exists(select 1 from pg_namespace where nspname = 'public') 最终获得了 TRUE。请将您的评论发布为答案。您知道 information_schema.schemata 和 pg_namespace 之间的区别吗？ - Marcio Mazzucato

Answer 1

我猜您看不到公共模式，可能是因为您使用的数据库角色无法测试模式的存在。实际上，information_schema.schemata是一个具有以下定义的视图：

 SELECT 
    current_database()::information_schema.sql_identifier AS catalog_name,
    n.nspname::information_schema.sql_identifier AS schema_name,
    u.rolname::information_schema.sql_identifier AS schema_owner, 
    NULL::character varying::information_schema.sql_identifier AS default_character_set_catalog,
    NULL::character varying::information_schema.sql_identifier AS default_character_set_schema,
    NULL::character varying::information_schema.sql_identifier AS default_character_set_name,
    NULL::character varying::information_schema.character_data AS sql_path
   FROM pg_namespace n, pg_authid u
  WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE'::text);

这也在文档中有描述。

您可以使用psql中的\d+ information_schema.schemata命令来获取information_schema中视图的定义。

在这种情况下，应该使用pg_namespace而不是information_schema.schemata。

- Tomas Greif

---

谢谢你的解释！你知道对于这个问题有什么最好的解决办法吗？ - Marcio Mazzucato

请按照Ruslan的回答中所述，使用pg_namespace代替。 - Tomas Greif

Answer 2

information_schema.schemata视图中的信息取决于您连接的角色，因此它并不是查询常规模式的正确视图。在9.3版本中，关于information_schema.schemata的文档如下所述：该视图包含当前数据库中由当前启用的角色拥有的所有模式。然而，仅从这个句子中不太清楚（至少对我来说）为什么无法看到public。在邮件列表帖子中，Tom Lane提供了更深入的解释：
请参见http://www.postgresql.org/message-id/[e-mail] 他的结论是：

“按目前的情况，非超级用户在这个视图中看不到“public”，“pg_catalog”，甚至是“information_schema”本身，这似乎有点愚蠢。”

这正好是这个问题的问题所在。最终建议：使用pg_namespace代替information_schema.schemata

---

在9.4版本中修改以符合用户的期望。当前文档如下所述：该视图包含当前用户可以访问的当前数据库中的所有模式（通过拥有某些特权或者是所有者）。在此视图中获取一个模式现在只需要对该模式具有USAGE特权即可。

- Daniel Vérité

---

非常感谢你的答案，非常详细！我打算使用pg_namespace而不是information_schema.schemata。 - Marcio Mazzucato

Answer 3

你写的那个查询应该是可以工作的... 试试这个替代方案：

select count(*) > 0 FROM information_schema.schemata WHERE schema_name = 'public';

- Bruno

---

我仍然收到FALSE。 - Marcio Mazzucato

Answer 4

（从评论中发布的答案）

直接引用pg_namespace表可能是一个不错的解决方法...

SELECT exists(select 1 from pg_namespace where nspname = 'public') as schema_exists;

我不知道确切的区别，但是我知道在PostgreSQL中，命名空间在内部“支持”模式。

另外，我相信那些系统pg_ *表不保证跨版本保持一致性，但至少自7.3以来就存在（http://www.postgresql.org/docs/7.4/static/catalog-pg-namespace.html），现在仍然存在（9.3.1）。

- Ruslan