如何检查给定模式中是否存在表 [英] How to check if a table exists in a given schema

问题描述

Postgres 8.4及更高版本的数据库包含 public 中的公共表和公司模式中的公司特定表。 >
公司模式名称始终以'company'开头，并以公司编号结尾。

所以可能有模式：

  public 
 company1 
 company2 
 company3 
 ... 
 companynn 
  

应用程序总是与单个公司合作。

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

  search_path ='company3，public'
  

表格存在于指定的 companyn 模式中？

  select isSpecific '，'tablenotincompany3schema'）
  

应返回 false 和

  select isSpecific（'company3'，'tableincompany3schema'）
  / pre> 
 
 
应返回 true 。
 
 
 
任何case函数应该只检查 companyn 传递的模式，而不检查其他模式。
 
 
 
 code> public 并传递模式，函数应该返回 true 。
 
它应该适用于Postgres 8.4 
 
 
   。
 
 
 
信息模式？
 
 
 
要找到表是否存在 ），严格来说，查询信息模式（ information_schema.tables ）是不正确，因为（文档）：
 
 
只显示当前用户访问
到（以拥有者或有一些特权的方式）的表和视图。
 
 
由@kong演示的查询 可以返回 FALSE ，但表仍可以存在。它回答了以下问题：
 
 
 
 如何检查表（或视图）是否存在，以及当前用户是否可以访问它？ >  
  SELECT EXISTS（
 SELECT 1 
 FROM information_schema.tables 
 WHERE table_schema ='schema_name'
 AND table_name ='table_name'
）; 
  
信息模式主要用于在主要版本和跨不同RDBMS之间保持可移植性。但是实现很慢，因为Postgres必须使用复杂的视图来符合标准（ information_schema.tables 是一个很简单的例子）。 
 
 
 
系统目录
系统目录 
 
 
您的问题是：
 
 
 
  如何检查表格是否存在？ strong> 
  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 
）; 
  
使用系统目录 pg_class 和 pg_namespace 直接，这也明显更快。但是，每个文档 pg_class  ：
 
 
目录 pg_class  
列，否则类似于表。这包括索引（但
另请参阅 pg_index ），序列， >，物化视图， composite 
 types 和 TOAST表; 
 
 
对于此特定问题，您还可以使用系统查看  pg_tables   。在主要的Postgres版本（这是几乎不关心这个基本查询）有点简单和更便携：
  SELECT EXISTS b $ b SELECT 1 
 FROM pg_tables 
 WHERE schemaname ='schema_name'
 AND tablename ='table_name'
）; 
  
标识符必须在上述所有如果您想询问：
 
 
 
  如何检查给定模式中的表格或类似对象的名称是否已生效？  
  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'
）; 
  
 
 
 
 
 
 相关答案在dba.SE讨论 信息模式与系统目录  
 
 
 
 
 
可选：转换为   regclass    
 
 
 
  SELECT'schema_name.table_name':: regclass 
  
如果（可选方案限定的）表（或占用该名称的其他对象）不存在，则引发异常。
 
 
 
如果不对表名进行模式限定，则转换为 regclass 默认为   search_path   并返回找到的第一个表的OID  - 如果表不在任何列出的模式中，则返回异常。请注意，系统模式 pg_catalog 和 pg_temp * （当前会话的临时对象的模式）  search_path 。
 
 
 
您可以使用它，并在函数中捕获可能的异常。示例：
 
 
 
 
 
 检查Postgres（plpgsql）中是否存在序列 
 
 
 
 
 
避免可能的异常，因此速度稍快。
 
 
 
  
 
  
现在更简单：
  SELECT to_regclass（'schema_name.table_name'）; 
  
与演员相同， 但，它会返回...  
 
 
 < blockquote> 
 
 ... null，而不是在找不到名称时抛出错误
 
 
 
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:
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:
search_path='company3,public'
How to check if a given table exists in a specified companyn schema?
select isSpecific('company3','tablenotincompany3schema')
should return false, and
select isSpecific('company3','tableincompany3schema')
should return true.

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

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.
解决方案
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):

  
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:

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.

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:

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


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.

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


Check if sequence exists in 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


                        
这篇关于如何检查给定模式中是否存在表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！