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

Postgres 8.4 and greater database contains common tables in public schema and company specific tables in company schema.
company schema names always start with 'company' and end with the company number.
So there may be schemas:

Postgres 8.4和更大的数据库包含公共模式中的公共表和公司模式中的特定公司表。公司架构名称总是以“公司”开头，以公司编号结束。所以可能有模式:

public
company1
company2
company3
...
companynn

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

应用程序总是与单个公司一起工作。在odbc或npgsql连接字符串中指定search_path，比如:

search_path='company3,public'

How to check if a given table exists in a specified companyn schema?

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

select isSpecific('company3','tablenotincompany3schema')

should return false, and

应该返回false,

select isSpecific('company3','tableincompany3schema')

should return true.

应该返回true。

In any case function should check only companyn schema passed, not other schemas.

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

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

如果在公共和传递模式中都存在一个给定的表，则该函数应该返回true。它应该适用于Postgres 8.4或更高版本。

3 个解决方案

#1

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 demonstrated by @kong can 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 1
   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.tables is a rather simple example). And some information (like OIDs) gets lost in translation from the system catalogs - which actually carry all information.

信息模式主要用于跨主要版本和不同RDBMS之间的移植。但是实现是缓慢的，因为Postgres必须使用复杂的视图来遵从标准(information_schema)。表是一个相当简单的例子。而一些信息(比如oid)从系统目录的翻译中丢失了——它实际上包含了所有的信息。

System catalogs

Your question was:

你的问题是:

How to check whether a table exists?

如何检查表是否存在?

SELECT EXISTS (
   SELECT 1 
   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_class and pg_namespace directly, which is also considerably faster. However, per documentation on pg_class:

直接使用系统目录pg_class和pg_namespace，这也相当快。然而，pg_class的每个文档:

The catalog pg_class catalogs 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 1 
   FROM   pg_tables
   WHERE  schemaname = 'schema_name'
   AND    tablename = 'table_name'
   );

Identifiers have to be unique among all objects 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 1 
   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。讨论“信息模式vs系统目录”

Alternative: cast to regclass

SELECT 'schema_name.table_name'::regclass

This raises an exception if 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 regclass defaults to the search_path and 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_catalog and pg_temp (the schema for temporary objects of the current session) are automatically part of the search_path.

如果您不使用schema-限定表名，那么将一个cast转换为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+

Much simpler now:

现在简单多了:

SELECT to_regclass('schema_name.table_name');

Same as the cast, but it returns ...

和演员一样，但它回来了……

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

…如果未找到名称，则不抛出错误。

#2

Perhaps use information_schema:

也许使用information_schema:

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

#3

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

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

我的旧SwissKnife库有三种风格:relname_exists(任何)、relname_规范化(任何东西)和relnamechecked_to_array(任何东西)。所有从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($1,'\.') t(x) -- not work with quoted names
     WHERE  CASE
              WHEN COALESCE(x[2],'')>'' THEN n.nspname = x[1]      AND c.relname = x[2]
              WHEN $2 IS NULL THEN           n.nspname = 'public'  AND c.relname = $1
              ELSE                           n.nspname = $2        AND c.relname = $1
            END
$f$ language SQL IMMUTABLE;

CREATE FUNCTION relname_exists(text,text default NULL) RETURNS boolean AS $wrap$
  SELECT EXISTS (SELECT relname_to_array($1,$2))
$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($1,$2), '.'), CASE WHEN $3 THEN '' ELSE NULL END)
$wrap$ language SQL IMMUTABLE;