如何检查 PostgreSQL 公共模式是否存在? [英] How to check if PostgreSQL public schema exists?

问题描述

运行以下查询:

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

我总是 FALSE，即使存在公共架构.

I am getting always FALSE, even if the public schema exists.

我应该如何检查这个模式是否存在?

How should i check if this schema exists?

编辑

我使用的是 PostgreSQL 8.4 版

I am using PostgreSQL version 8.4

推荐答案

information_schema.schemata 中的信息取决于您所连接的角色，因此它并不是真正适合查询的视图发现一般模式.

The information from information_schema.schemata depends on the role you're connected with, so it's not really the right view to query to discover schemas in general.

information_schema.schemata 中的 文档9.3 说:

视图模式包含当前数据库中的所有模式由当前启用的角色所有.

The view schemata contains all schemas in the current database that are owned by a currently enabled role.

然而，仅仅从那句话中并不清楚(至少对我而言)，为什么你看不到 public .

However it's not quite clear (at least to me) from just that sentence, why you can't see public .

在邮件列表帖子中，Tom Lane 有一个更进一步的解释:
见 http://www.postgresql.org/message-id/[e-mail]

In a mailing-list post, Tom Lane has an explanation the goes a bit further:
See http://www.postgresql.org/message-id/[e-mail]

他的结论:

就目前情况而言，非超级用户不会看到public"、pg_catalog"或在这个视图中甚至是information_schema"本身，这似乎有点傻逼.

As things stand, a non-superuser won't see "public", "pg_catalog", nor even "information_schema" itself in this view, which seems a tad silly.

这看起来与这个问题中的问题完全一样.

which looks exactly like the problem in this question.

底线:使用 pg_namespace 而不是 information_schema.schemata

Bottom line: use pg_namespace instead of information_schema.schemata

这在 9.4 版中进行了修改，以符合用户的期望.当前文档 说:

This was amended in version 9.4 to conform to what users expect. The current doc says:

视图模式包含当前数据库中的所有模式当前用户有权访问(通过成为所有者或拥有一些特权).

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).

架构上的

USAGE 权限现在足以从该视图中获取它.

USAGE privilege on a schema is now enough to get it from this view.

这篇关于如何检查 PostgreSQL 公共模式是否存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！