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

Question

Postgres 8.4 和更高版本的数据库在 public 模式中包含公共(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 或更高版本。

Answer 1

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

信息架构？

要查找“表是否存在”(不管是谁问)，查询信息模式(information_schema.tables)是不正确 , 严格来说，因为 ( per documentation ):

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

查询provided by @kong可以返回 FALSE，但表仍然存在。它回答了这个问题:

如何检查一个表(或 View )是否存在，以及当前用户是否有访问权限？

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

信息模式主要用于保持跨主要版本和跨不同 RDBMS 的可移植性。但是实现速度很慢，因为 Postgres 必须使用复杂的 View 来符合标准(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，这也相当快。然而，per documentation on pg_class :

The catalog pg_class catalogs tables and most everything else that hascolumns or is otherwise similar to a table. This includes indexes (butsee also pg_index), sequences, views, materialized views, compositetypes, and TOAST tables;

对于这个特定问题，您还可以使用 system view 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'
   );

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

备选方案:转换为 regclass

SELECT 'schema_name.table_name'::regclass;

如果(可选的模式限定的)表(或占用该名称的其他对象)不存在，这会引发异常。

如果您不对表名进行模式限定，则转换为 regclass 默认为 search_path并返回找到的第一个表的 OID - 如果表不在任何列出的模式中，则返回异常。请注意，系统架构 pg_catalog 和 pg_temp(当前 session 的临时对象的架构)自动成为 search_path 的一部分。

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

• Check if sequence exists in Postgres (plpgsql)

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

请注意，名称的每个组成部分在这里都被视为标识符 - 与上面的名称作为文字字符串给出的查询相反。标识符转换为小写，除非双引号。如果您用双引号强制使用其他非法标识符，则需要包括这些标识符。喜欢:

SELECT '"Dumb_SchName"."FoolishTbl"'::regclass;

参见:

• Are PostgreSQL column names case-sensitive?

to_regclass(rel_name)在 Postgres 9.4+ 中

现在简单多了:

SELECT to_regclass('schema_name.table_name');

与 Actor 相同，but it returns ...

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