如何检查给定模式中是否存在表

Question

Postgres 8.4 和更高版本的数据库包含 — 模式中的公用表和 — company public 模式中的公司特定表。

company 模式名称始终以 'company' 开头并以公司编号结尾。

所以可能有这样的模式：

 public
company1
company2
company3
...
companynn

应用程序始终适用于一家公司。

search_path 在 odbc 或 npgsql 连接字符串中相应指定，例如：

 search_path='company3,public'

您将如何检查给定的表是否存在于指定的 companyn 模式中？

例如：

 select isSpecific('company3','tablenotincompany3schema')

应该返回 false ，并且

select isSpecific('company3','tableincompany3schema')

应该返回 true 。

在任何情况下，该函数都应该只检查 companyn 模式已通过，而不是其他模式。

如果给定的表在 public 和传递的模式中都存在，则函数应该返回 true 。

它应该适用于 Postgres 8.4 或更高版本。

原文由 Andrus 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

这取决于您要 准确 测试的内容。

信息架构？

要查找“表是否存在”（ _无论是谁询问_），查询信息架构（ information_schema.tables ）是 不正确 的，严格来说，因为（ 根据文档）：

仅显示当前用户有权访问的那些表和视图（通过成为所有者或具有某些特权）。

@kong 提供 的查询可以返回 FALSE ，但表仍然可以存在。它回答了这个问题：

如何检查表（或视图）是否存在，当前用户是否可以访问它？

 SELECT EXISTS (
   SELECT FROM information_schema.tables
   WHERE  table_schema = 'schema_name'
   AND    table_name   = 'table_name'
   );

信息模式主要用于保持跨主要版本和跨不同 RDBMS 的可移植性。但是实现起来很慢，因为 Postgres 必须使用复杂的视图来遵守标准（ information_schema.tables 是一个相当简单的例子）。并且一些信息（如 OID）在系统目录的翻译中丢失了——这些目录 实际上 携带了所有信息。

系统目录

你的问题是：

如何检查表是否存在？

 SELECT EXISTS (
   SELECT FROM pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = 'schema_name'
   AND    c.relname = 'table_name'
   AND    c.relkind = 'r'    -- only tables
   );

直接使用系统目录 pg_class 和 pg_namespace 也快很多。但是， 根据关于 pg_class 的文档：

目录 pg_class 对表和大多数其他具有列或与表类似的所有内容进行编目。这包括 索引（但另见 pg_index ）、 序列、 视图、 物化视图、 复合类型 和 TOAST 表；

对于这个特定问题，您还可以使用 系统视图 pg_tables 。跨主要 Postgres 版本更简单、更便携（这个基本查询几乎不关心）：

 SELECT EXISTS (
   SELECT FROM pg_tables
   WHERE  schemaname = 'schema_name'
   AND    tablename  = 'table_name'
   );

标识符在上述 所有 对象中必须是唯一的。如果你想问：

如何检查给定模式中的表或类似对象的名称是否被采用？

 SELECT EXISTS (
   SELECT FROM pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = 'schema_name'
   AND    c.relname = 'table_name'
   );

• dba.SE 上讨论 “信息模式与系统目录” 的相关答案

替代方案：强制转换为 regclass

 SELECT 'schema_name.table_name'::regclass;

如果（可选的模式限定）表（或占用该名称的其他对象）不存在，则会 _引发异常_。

如果您不对表名进行模式限定，则强制转换为 regclass 默认为 search_path 并返回找到的第一个表的 OID - 如果表不存在则返回异常列出的架构。请注意，系统模式 pg_catalog 和 pg_temp （当前会话的临时对象的模式）自动成为 search_path 的一部分。

您可以使用它并在函数中捕获可能的异常。例子：

• 检查 Postgres 中是否存在序列（plpgsql）

像上面这样的查询避免了可能的异常，因此速度稍快。

请注意，名称的每个组件在这里都被视为 标识符- 与上面的查询相反，其中名称作为文字字符串给出。除非双引号，否则标识符将转换为小写。如果您使用双引号强制使用其他非法标识符，则需要包含这些标识符。喜欢：

 SELECT '"Dumb_SchName"."FoolishTbl"'::regclass;

看：

• PostgreSQL 列名是否区分大小写？

to_regclass(rel_name) 在 Postgres 9.4+

现在简单多了：

 SELECT to_regclass('schema_name.table_name');

与演员相同， 但 它返回……

… null 而不是在找不到名称时抛出错误

原文由 Erwin Brandstetter 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

来自 https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PATTERNS

例如，\dt foo*。 bar 显示所有表名包含 bar 的表，这些表位于模式名以 foo 开头的模式中。

原文由 jian 发布，翻译遵循 CC BY-SA 4.0 许可协议