如何检查给定架构中是否存在表

小编典典

如何检查给定架构中是否存在表

sql

Postgres 8.4及更高版本的数据库在public架构中包含公用表，在架构中包含公司特定的表company。
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或更高版本。

阅读 203

2021-04-28

共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上讨论“信息模式与系统目录”的相关答案
替代方法：强制转换为 regclassSELECT 'schema_name.table_name'::regclass

这引发一个例外，如果（任选模式修饰）表（或其它物体占据该名称）不存在。

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

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

检查Postgres（plpgsql）中是否存在序列
上面的查询避免了可能的异常，因此速度稍快。

to_regclass(rel_name) 在Postgres 9.4+

现在简单得多：

SELECT to_regclass('schema_name.table_name');

与演员表相同，但返回…

… null而不是如果找不到名称则抛出错误

2021-04-28