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

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

public
company1
company2
company3
...
companynn

应用程序始终与单个公司一起使用。
在odbc或npgsql连接字符串中相应地指定了search_path，例如：

search_path='company3,public'

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

例如：

select isSpecific('company3','tablenotincompany3schema')

应返回false和

select isSpecific('company3','tableincompany3schema')

应该返回true。

无论如何，该函数应仅检查传递的companyn模式，而不检查其他模式。

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

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

dba.SE的相关答案在讨论“信息模式”相对于系统目录”

替代方法：强制转换为regclass

）表（或其他占用该名称的对象）不存在。 -如果表不在列出的架构中，则为例外。请注意，系统架构regclass和search_path（当前会话的临时对象的架构）自动成为pg_catalog的一部分。

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

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

上面的查询避免了可能的异常，因此速度稍快。 Postgres 9.4+中的/>
pg_temp现在更简单了：

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

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

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

从外壳程序：[[`psql dbname -tAc“选择存在（从information_schema.tables中选择1，其中table_schema ='ejabberd'AND table_name ='用户'）；”`='t']]]

–brauliobo
2015年3月8日，0：46

您是否没有使用pg_tables的任何原因？

–m0meni
17年1月9日在16:46

pg_tables实际上是“如何检查表是否存在？”的好主意。（仅检查表，而不是用于其他目的，如上文所述。此外，pg_tables是包含多个表（pg_class，pg_namespace，pg_tablespace）的视图，这有点贵。最重要的原因：我习惯于查询pg_class直接，并且在编写此答案时没有想到pg_tables。谢谢，我现在在上面添加了它。

–欧文·布兰德斯特（Erwin Brandstetter）
17年1月10日在4:48

@ sage88：是的，我删除了我不正确的评论。您可以使用pg_my_temp_schema（）获取实际临时模式的OID（如果存在）。（但是information_schema中的视图不包含OID。您可以从pg_namespace中选择nspname，其中OID = pg_my_temp_schema（））您的测试存在一些弱点。正确的测试应为table_schema，例如“ pg \ _temp \ _％”或更严格：table_schema〜'^ pg_temp_ \ d + $'。

–欧文·布兰德斯特（Erwin Brandstetter）
17年1月23日在0:03

@PeterKrauss如果尝试在9.4之前的postgres版本上使用to_regclass函数，将会收到该错误。必须有9.4+

–spetz83
17年11月14日在16:20

#2 楼

也许使用information_schema：

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

#3 楼

对于PostgreSQL 9.3或更低版本...或者谁喜欢将其标准化为text

我的旧SwissKnife库的三种风格：relname_exists(anyThing)，relname_normalized(anyThing)和relnamechecked_to_array(anyThing)。从pg_catalog.pg_class表中进行所有检查，并返回标准的通用数据类型（布尔，文本或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;

编程黑洞网

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

#1 楼

评论

#2 楼

#3 楼