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

How to check if a table exists in a given schema

提问by Andrus

Postgres 8.4 and greater databases contain common tables in publicschema and company specific tables in companyschema.
companyschema names always start with 'company'and end with the company number.
So there may be schemas like:

Postgres 8.4 和更高版本的数据库包含public模式中的公共表和模式中的公司特定表company。
company架构名称始终'company'以公司编号开头和结尾。
所以可能有如下模式：

public
company1
company2
company3
...
companynn

An application always works with a single company.
The search_pathis specified accordingly in odbc or npgsql connection string, like:

应用程序始终适用于一家公司。
的search_path相应指定在ODBC或连接Npgsql的字符串，如：

search_path='company3,public'

How would you check if a given table exists in a specified companynschema?

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

eg:

例如：

select isSpecific('company3','tablenotincompany3schema')

should return false, and

应该返回false，并且

select isSpecific('company3','tableincompany3schema')

should return true.

应该返回true。

In any case, the function should check only companynschema passed, not other schemas.

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

If a given table exists in both publicand the passed schema, the function should return true.
It should work for Postgres 8.4 or later.

如果给定的表同时存在于public传递的模式中，则该函数应返回true.
它应该适用于 Postgres 8.4 或更高版本。

回答by Erwin Brandstetter

It depends on what you want to test exactly.

这取决于您要准确测试什么。

Information schema?

信息架构？

To find "whether the table exists" (no matter who's asking), querying the information schema (information_schema.tables) is incorrect, strictly speaking, because (per documentation):

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

Only those tables and views are shown that the current user has access to (by way of being the owner or having some privilege).

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

The query provided by @kongcan return FALSE, but the table can still exist. It answers the question:

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

How to check whether a table (or view) exists, and the current user has access to it?

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

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

The information schema is mainly useful to stay portable across major versions and across different RDBMS. But the implementation is slow, because Postgres has to use sophisticated views to comply to the standard (information_schema.tablesis a rather simple example). And some information (like OIDs) gets lost in translation from the system catalogs - which actuallycarry all information.

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

System catalogs

系统目录

Your question was:

你的问题是：

How to check whether a table exists?

如何检查表是否存在？

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

Use the system catalogs pg_classand pg_namespacedirectly, which is also considerably faster. However, per documentation on pg_class:

直接使用系统目录pg_class和pg_namespace，这也快得多。但是，根据以下文档pg_class：

The catalog pg_classcatalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes(but see also pg_index), sequences, views, materialized views, composite types, and TOAST tables;

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

For this particular question you can also use the system view pg_tables. A bit simpler and more portable across major Postgres versions (which is hardly of concern for this basic query):

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

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

Identifiers have to be unique among allobjects mentioned above. If you want to ask:

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

How to check whether a name for a table or similar object in a given schema is taken?

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

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'
   );

• Related answer on dba.SE discussing "Information schema vs. system catalogs"

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

Alternative: cast to regclass

替代方法：投射到 regclass

SELECT 'schema_name.table_name'::regclass

This raises an exceptionif the (optionally schema-qualified) table (or other object occupying that name) does not exist.

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

If you do not schema-qualify the table name, a cast to regclassdefaults to the search_pathand returns the OID for the first table found - or an exception if the table is in none of the listed schemas. Note that the system schemas pg_catalogand pg_temp(the schema for temporary objects of the current session) are automatically part of the search_path.

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

You can use that and catch a possible exception in a function. Example:

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

• Check if sequence exists in Postgres (plpgsql)

• 检查 Postgres (plpgsql) 中是否存在序列

A query like above avoids possible exceptions and is therefore slightly faster.

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

to_regclass(rel_name)in Postgres 9.4+

to_regclass(rel_name)在 Postgres 9.4+

Much simpler now:

现在简单多了：

SELECT to_regclass('schema_name.table_name');

Same as the cast, butit returns ...

与演员表相同，但它返回......

... null rather than throwing an error if the name is not found

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

回答by kong

Perhaps use information_schema:

也许使用information_schema：

SELECT EXISTS(
    SELECT * 
    FROM information_schema.tables 
    WHERE 
      table_schema = 'company3' AND 
      table_name = 'tableincompany3schema'
);

回答by Peter Krauss

For PostgreSQL 9.3 or less...Or who likes all normalized to text

对于 PostgreSQL 9.3 或更低版本...或者谁喜欢全部标准化为文本

Three flavors of my old SwissKnife library: relname_exists(anyThing), relname_normalized(anyThing)and relnamechecked_to_array(anyThing). All checks from pg_catalog.pg_classtable, and returns standard universal datatypes (boolean, textor text[]).

我的旧 SwissKnife 库的三种风格：relname_exists(anyThing),relname_normalized(anyThing)和relnamechecked_to_array(anyThing). 所有来自pg_catalog.pg_class表的检查，并返回标准通用数据类型（boolean、text或text[] ）。

/**
 * From my old SwissKnife Lib to your SwissKnife. License CC0.
 * Check and normalize to array the free-parameter relation-name.
 * Options: (name); (name,schema), ("schema.name"). Ignores schema2 in ("schema.name",schema2).
 */
CREATE FUNCTION relname_to_array(text,text default NULL) RETURNS text[] AS $f$
     SELECT array[n.nspname::text, c.relname::text]
     FROM   pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace,
            regexp_split_to_array(,'\.') t(x) -- not work with quoted names
     WHERE  CASE
              WHEN COALESCE(x[2],'')>'' THEN n.nspname = x[1]      AND c.relname = x[2]
              WHEN  IS NULL THEN           n.nspname = 'public'  AND c.relname = 
              ELSE                           n.nspname =         AND c.relname = 
            END
$f$ language SQL IMMUTABLE;

CREATE FUNCTION relname_exists(text,text default NULL) RETURNS boolean AS $wrap$
  SELECT EXISTS (SELECT relname_to_array(,))
$wrap$ language SQL IMMUTABLE;

CREATE FUNCTION relname_normalized(text,text default NULL,boolean DEFAULT true) RETURNS text AS $wrap$
  SELECT COALESCE(array_to_string(relname_to_array(,), '.'), CASE WHEN  THEN '' ELSE NULL END)
$wrap$ language SQL IMMUTABLE;